package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.WordReportApplication;
import com.dhcc.DRGsHosp.infra.util.Arith;

/**
 * @Author:LiuHongjun
 * @Description:
 * @Date:2016年12月29日 下午9:18:38
 * @version :2.3.2
 * @copyright:DHC 2015-2016
 */
@Named
@Transactional
public class WordReportApplicationImpl implements WordReportApplication {

	private EntityRepository getRepository() {
		EntityRepository repository = null;
		if (repository == null) {
			repository = InstanceFactory.getInstance(EntityRepository.class,
					"repository");
		}
		return repository;
	}

	/**
	 * 
	 * @methodname :findHospBaseQuota
	 * @description 
	 *              :获取全院基本指标(病例开始时间、结束时间，全院出院病例总数，涉及科室数量,全院大于60天病例数,全院病例住院总天数，总费用
	 *              ，总医药费,死亡病例数)
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :Object[]
	 * @date :2016年3月29日
	 */
	public Object[] findHospBaseQuota(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String sql = "SELECT MIN(T.OUT_DATE) DATEFROM,"
				+ "MAX(T.OUT_DATE) DATETO,"
				+ "COUNT(*) HOSPCASECOUNT,"
				+ "COUNT(DISTINCT(T.OUT_DEP_CODE)) HOSPDEPCOUNT,"
				+ "SUM(CASE WHEN T.IN_DAYS > 60 THEN 1 ELSE 0 END) HOSPCASE60DAYSGTCOUNT,"
				+ "SUM(T.IN_DAYS) CASETITLEDAYS,"
				+ "SUM(T.COSTAMOUNT) CASETITLECOST,"
				+ "SUM(T.DRUGCOST) CASETITLEDRUGCOST,"
				+ "SUM(CASE WHEN T.OUT_WAY = 5 THEN 1 ELSE 0 END) CASEDEAD "
				+ "FROM DRGS.T_MEDICALRECORD T WHERE to_char(T.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'";
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql)
				.singleResult();
		return hospBaseQuota;
	}

	public Object[] findSimpHospBaseQuota(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		
		StringBuffer sql = new StringBuffer();
		  sql.append("    select count(*) allcnt,   \n");
		  sql.append("    nvl(sum(case when pd.pardrg_code is not null and mr.in_days < 61 then  1 else 0 end),0) as incnt,   \n");
		  sql.append("    nvl(sum(case when mr.in_days >= 61 then  1 else 0 end),0) as over60cnt,   \n");
		  sql.append("    nvl(round(sum(case when pd.pardrg_code is not null and mr.in_days < 61 then  1 else 0 end)/sum(case when mr.in_days < 61 then  1 else 0 end)*100,2),0) InRatio, \n");
		  sql.append("    nvl(sum(case when pd.pardrg_code is null and pd.parid is not null and mr.in_days < 61 then  1 else 0 end),0) as notincnt   \n");
		  sql.append("    from drgs.t_medicalrecord mr left join drgs.t_patientdrg pd   \n");
		  sql.append("    on mr.id = pd.parid   \n");
		  sql.append("    left join drgs.t_department dep   \n");
		  sql.append("    on mr.out_dep_code = dep.dep_code   \n");
		  sql.append("   left join drgs.t_depgroup dg   \n");
		  sql.append("    on dg.depgroup_code = dep.pardepgroup_code   \n");
		  sql.append("    where to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   \n");
		
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return hospBaseQuota;
	}

	/**
	 * 
	 * @methodname :findHospCaseOperationCount
	 * @description :获取全院手术病例总数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :String
	 * @date :2016年3月29日
	 */
	public String findHospCaseOperationCount(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		String sql = "SELECT COUNT(A.PARID)  FROM DRGS.T_MEDICALRECORD B  JOIN DRGS.T_OPERATION A ON A.PARID = B.ID WHERE A.OPER_TYPE='M' AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'";
		String caseOperationCount = getRepository().createSqlQuery(sql)
				.singleResult().toString();
		return caseOperationCount;
	}

	/**
	 * 
	 * @methodname :findHospCase60DaysgtCount
	 * @description :获取全院入组病例总数、DRGs组数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :Object[]
	 * @date :2016年3月29日
	 */
	public Object[] findHospCaseDRGsCount(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		String sql = "SELECT COUNT(A.PARID) CASECOUNT,COUNT(DISTINCT(A.PARDRG_CODE)) DRGSCOUNT,COUNT(DISTINCT(A.MDC)) FROM DRGS.T_PATIENTDRG A WHERE A.PARDRG_CODE IS NOT NULL AND EXISTS (SELECT B.ID FROM DRGS.T_MEDICALRECORD B WHERE A.PARID = B.ID AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' )";
		Object[] caseDRGsCount = getRepository().createSqlQuery(sql)
				.singleResult();
		return caseDRGsCount;
	}

	/**
	 * 
	 * @methodname :findHospCaseDRGsWeightCount
	 * @description :获取全院有权重DRGs组的入组病例数、总权重(用于计算CMI)
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :Object[]
	 * @date :2016年3月29日
	 */
	public Object[] findHospCaseDRGsWeightCount(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT COUNT(A.PARID),SUM(T.AS_WEIGHT) FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "'AND EXISTS (SELECT B.ID FROM DRGS.T_MEDICALRECORD B WHERE A.PARID = B.ID AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')";
		Object[] hospCaseDRGsWeightCount = getRepository().createSqlQuery(sql)
				.singleResult();
		return hospCaseDRGsWeightCount;
	}

	/**
	 * 
	 * @methodname :findHospCaseFeeEffAndTimeEff
	 * @description :获取全院病例费用消耗指数,全院病例时间消耗指数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :Object[]
	 * @date :2016年3月29日
	 */
	public Object[] findHospCaseFeeEffAndTimeEff(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID) FROM  DRGS.T_MEDICALRECORD E RIGHT JOIN (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T WHERE A.pardrg_code is not null AND A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' AND EXISTS (SELECT B.ID FROM DRGS.T_MEDICALRECORD B WHERE A.PARID = B.ID AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G  ON E.ID = G.PARID ";
		Object[] hospCaseFeeEffAndTimeEff = getRepository().createSqlQuery(sql)
				.singleResult();
		return hospCaseFeeEffAndTimeEff;
	}

	/**
	 * 
	 * @Description:
	 * @param paramMap
	 * @return
	 */
	public String findHospCaseRisk1Ratio(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("	select case when Risk1CaseCnt<>0 then Risk1DeadCaseCnt/Risk1CaseCnt*100 else 0 end risk1Ratio  \n");
		sql.append(" 	from ( select sum(case when c.as_risk_level=1 then 1 else 0 end) as Risk1CaseCnt,\n");
		sql.append("   sum(case when a.out_way='5' and c.as_risk_level=1 then 1 else 0 end) as Risk1DeadCaseCnt \n");
		sql.append(" 	from DRGs.t_medicalrecord a left join DRGs.t_patientdrg b on a.id=b.parid \n");
		sql.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE \n");
		sql.append("  left join drgs.t_adrg adrg on adrg.ADRG_CODE=drg.ADRG_CODE and drg.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join DRGs.t_department dep on a.out_dep_code=dep.dep_code \n");
		sql.append(" left join DRGs.t_Depgroup depgroup on depgroup.depgroup_code=dep.pardepgroup_code \n");
		sql.append(" left join DRGs.t_annualstd c on b.pardrg_code=c.as_drg_code and as_year=:benchmarkYear   left join DRGs.t_medins d on d.parid=a.id \n");
		sql.append(" 	where to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') a  \n");
		String hospCaseRisk1Ratio = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map)
				.singleResult().toString();
		return hospCaseRisk1Ratio;
	}

	/**
	 * 
	 * @Description:
	 * @param paramMap
	 * @return
	 */
	public String findHospCaseRisk2Ratio(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("select case when Risk2CaseCnt<>0 then Risk2DeadCaseCnt/Risk2CaseCnt*100 else 0 end risk1Ratio  \n");
		sql.append(" from ( select sum(case when c.as_risk_level=2 then 1 else 0 end) as Risk2CaseCnt,\n");
		sql.append(" sum(case when a.out_way='5' and c.as_risk_level=2 then 1 else 0 end) as Risk2DeadCaseCnt \n");
		sql.append(" from DRGs.t_medicalrecord a left join DRGs.t_patientdrg b on a.id=b.parid \n");
		sql.append(" left join drgs.t_drgs drg on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE \n");
		sql.append("  left join drgs.t_adrg adrg on adrg.ADRG_CODE=drg.ADRG_CODE and drg.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join drgs.t_mdcgroup mdc on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE \n");
		sql.append(" left join DRGs.t_department dep on a.out_dep_code=dep.dep_code \n");
		sql.append(" left join DRGs.t_Depgroup depgroup on depgroup.depgroup_code=dep.pardepgroup_code \n");
		sql.append(" left join DRGs.t_annualstd c on b.pardrg_code=c.as_drg_code and as_year=:benchmarkYear   left join DRGs.t_medins d on d.parid=a.id \n");
		sql.append(" where to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') a  \n");
		String hospCaseRisk2Ratio = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map)
				.singleResult().toString();
		return hospCaseRisk2Ratio;
	}

	/**
	 * 
	 * @Description:
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findMdcCW(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT e.as_mdc_code, e.mdc_desc, e.mdcmi,e.weight  \n");
		sql.append("  FROM (SELECT t.as_mdc_code, m.mdc_desc, sum(T.AS_WEIGHT) / count(A.PARID) mdcmi,sum(T.AS_WEIGHT) weight \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGs.T_MDCGROUP m  \n");
		sql.append("  WHERE A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_YEAR =:benchmarkYear \n");
		sql.append(" and a.ver_code = m.ver_code AND A.PARDRG_CODE is NOT NULL and t.as_mdc_code = m.mdc_code   AND EXISTS \n");
		sql.append(" (SELECT B.ID  FROM DRGS.T_MEDICALRECORD B  WHERE A.PARID = B.ID AND B.in_days <61 \n");
		sql.append(" AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')  \n");
		sql.append(" group by t.as_mdc_code, m.mdc_desc) e  order by e.mdcmi desc \n");
		List<Object[]> mdcTableInfoByCMIList = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();

		return mdcTableInfoByCMIList;
	}

	/**
	 * 
	 * @methodname :findCapacityMDCByTotalWeight
	 * @description :获取前五的MDC信息根据總權重排名
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findCapacityMDCByTotalWeight(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT b.MDC,d.MDC_DESC,SUM(c.AS_WEIGHT) mdctotalweight FROM drgs.t_medicalrecord a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b ON a.ID =b.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_mdcgroup d on c.AS_MDC_CODE=d.MDC_CODE \n");
		sql.append(" WHERE c.AS_WEIGHT is not NULL  AND a.in_days < 61 AND b.VER_CODE=d.VER_CODE \n");
		sql.append(" AND c.AS_YEAR=:benchmarkYear  \n");
		sql.append(" AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'\n");
		sql.append(" GROUP BY  b.MDC,d.MDC_DESC ORDER BY mdctotalweight DESC\n");
		List<Object[]> mdcTableInfoByTotalWeightList = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();

		return mdcTableInfoByTotalWeightList;
	}

	/**
	 * 
	 * @Description:
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findCapacityDepInfoByDRGsCountList(
			Map<String, Object> paramMap) {
		Map<String, Object> map = new HashMap<String, Object>();
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT b.out_dep_code,b.out_dep, count(distinct(a.pardrg_code)) drgCount, count(a.parid) \n");
		sql.append(" FROM DRGS.T_PATIENTDRG a, DRGs.t_Medicalrecord b \n");
		sql.append(" WHERE A.PARDRG_CODE is not null and a.parid = b.id and b.in_days <61 \n");
		sql.append("  and to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" group by  b.out_dep_code,b.out_dep order by drgCount desc \n");
		List<Object[]> capacityDepInfoByDRGsCountList = getRepository()
				.createSqlQuery(sql.toString()).list();
		return capacityDepInfoByDRGsCountList;
	}

	public List<Object[]> findCapacityDepInfoByWeightList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT b.out_dep_code,  b.out_dep, round(sum(T.AS_WEIGHT) ,2) as depweight,  count(a.parid) \n");
		sql.append(" FROM DRGS.T_PATIENTDRG a,  DRGS.T_ANNUALSTD T ,DRGs.t_Medicalrecord b \n");
		sql.append(" WHERE  A.PARDRG_CODE = T.AS_DRG_CODE   AND T.AS_YEAR =:benchmarkYear \n");
		sql.append(" and A.PARDRG_CODE is not null and a.parid = b.id and b.in_days <61 and T.as_weight is not null \n");
		sql.append(" and to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append(" group by b.out_dep_code,  b.out_dep order by depweight desc \n");
		List<Object[]> capacityDepInfoByDRGsCountList = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return capacityDepInfoByDRGsCountList;
	}

	/**
	 * 获取CMI排名科室情况表格信息
	 */
	public List<Object[]> findCapacityDepInfoByCMIList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		String sql = "SELECT b.out_dep_code, b.out_dep,round(SUM(T.AS_WEIGHT) / COUNT(A.PARID),2) cmiValue "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGS.T_MEDICALRECORD B "
				+ " WHERE A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_YEAR = :benchmarkYear AND A.PARID = B.ID and B.in_days <61 AND T.AS_WEIGHT IS NOT NULL  "
				+ " AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  AND A.PARDRG_CODE IS NOT NULL "
				+ " group by b.out_dep_code, b.out_dep order by cmiValue desc";
		List<Object[]> depTableInfoByCMIList = getRepository()
				.createSqlQuery(sql).setParameters(map).list();

		return depTableInfoByCMIList;
	}

	/**
	 * 
	 * @methodname :findEfficiencyMDCTableByFeeEffList
	 * @description :获取费用消耗指数专业排名表信息
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findEfficiencyMDCTableByFeeEffList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.MDC,G.MDC_DESC,round(SUM(E.COSTAMOUNT / G.AS_AVG_FEE) / COUNT(G.PARID),2) FEE,COUNT(G.PARID) "
				+ " FROM DRGS.T_MEDICALRECORD E "
				+ " RIGHT JOIN (SELECT A.PARID, A.PARDRG_CODE, T.AS_AVG_FEE,A.MDC,MC.MDC_DESC "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T,DRGS.T_MDCGROUP MC "
				+ " WHERE A.PARDRG_CODE = T.AS_DRG_CODE "
				+ " AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' AND A.MDC = MC.MDC_CODE AND MC.VER_CODE=A.VER_CODE "
				+ " AND EXISTS "
				+ " (SELECT B.ID "
				+ " FROM DRGS.T_MEDICALRECORD B "
				+ " WHERE A.PARID = B.ID AND  B.IN_DAYS <61"
				+ " AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ " ON E.ID = G.PARID "
				+ " group by G.MDC,G.MDC_DESC ORDER BY FEE DESC";
		List<Object[]> mdcTableInfoByFeeEffList = getRepository()
				.createSqlQuery(sql).list();
		return mdcTableInfoByFeeEffList;
	}

	/**
	 * 
	 * @methodname :findEfficiencyMDCTableByTimeEffList
	 * @description :获取时间消耗指数专业排名表信息
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findEfficiencyMDCTableByTimeEffList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.MDC,G.MDC_DESC,round(SUM(E.IN_DAYS / G.AS_AVG_DAYS) / COUNT(G.PARID),2) TIMEEFF,COUNT(G.PARID) "
				+ " FROM DRGS.T_MEDICALRECORD E "
				+ " RIGHT JOIN (SELECT A.PARID, A.PARDRG_CODE, T.AS_AVG_DAYS,A.MDC,MC.MDC_DESC "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T,DRGS.T_MDCGROUP MC "
				+ " WHERE A.PARDRG_CODE = T.AS_DRG_CODE "
				+ " AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' AND A.MDC = MC.MDC_CODE AND MC.VER_CODE=A.VER_CODE "
				+ " AND EXISTS "
				+ " (SELECT B.ID "
				+ "  FROM DRGS.T_MEDICALRECORD B "
				+ "  WHERE A.PARID = B.ID and b.in_days <61 "
				+ "   AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ "  ON E.ID = G.PARID "
				+ " group by G.MDC,G.MDC_DESC ORDER BY TIMEEFF DESC";
		List<Object[]> mdcTableInfoByTimeEffList = getRepository()
				.createSqlQuery(sql).list();
		return mdcTableInfoByTimeEffList;
	}

	/**
	 * 
	 * @Description: 获取科室的时间费用消耗指数
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findEfficiencyDepInfoList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT E.OUT_DEP_CODE,E.OUT_DEP,round(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2), \n");
		sql.append(" round(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  \n");
		sql.append(" FROM  DRGS.T_MEDICALRECORD E RIGHT JOIN (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear \n");
		sql.append(" AND EXISTS (SELECT B.ID FROM DRGS.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  \n");
		sql.append(" AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append(" )) G  ON E.ID = G.PARID GROUP BY  E.OUT_DEP_CODE,E.OUT_DEP \n");
		sql.append(" ORDER BY round(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC, round(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  DESC\n");
		List<Object[]> efficiencyDepInfoList = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return efficiencyDepInfoList;
	}

	/**
	 * 
	 * @methodname :findQualityMDCTableByRisk1List
	 * @description :获取低风险死亡人数各MDC分布情况表
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findQualityMDCTableByRisk1List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.mdc,G.mdc_desc,COUNT(G.PARID), "
				+ " SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) caseDeadCount, "
				+ "  SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) / COUNT(G.PARID) caseDeadRatio "
				+ "  FROM DRGS.T_MEDICALRECORD E "
				+ "  RIGHT JOIN (SELECT A.PARID,a.mdc,mdc.mdc_desc "
				+ "  FROM DRGS.T_PATIENTDRG A, DRGS.t_Mdcgroup mdc WHERE  A.MDC = mdc.mdc_code and a.ver_code = mdc.ver_code "
				+ "   and A.PARDRG_CODE IN "
				+ "   (SELECT T.AS_DRG_CODE "
				+ "     FROM DRGS.T_ANNUALSTD T "
				+ "   WHERE T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ "    AND T.AS_RISK_LEVEL = '1') "
				+ "   AND EXISTS "
				+ "  (SELECT B.ID "
				+ "   FROM DRGS.T_MEDICALRECORD B "
				+ "  WHERE A.PARID = B.ID AND B.IN_DAYS < 61 "
				+ "   AND to_char(B.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ "   ON E.ID = G.PARID "
				+ "  group by G.mdc having SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) > 0 order by caseDeadCount desc";
		List<Object[]> qualityMDCTableByRisk1List = getRepository()
				.createSqlQuery(sql).list();
		return qualityMDCTableByRisk1List;
	}

	/**
	 * 
	 * @methodname :findQualityMDCTableByRisk2List
	 * @description :获取中低风险死亡人数各MDC分布情况表
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findQualityMDCTableByRisk2List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.mdc,G.mdc_desc,COUNT(G.PARID), "
				+ "  SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) caseDeadCount, "
				+ "  SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) / COUNT(G.PARID) caseDeadRatio "
				+ "  FROM DRGS.T_MEDICALRECORD E "
				+ "  RIGHT JOIN (SELECT A.PARID,a.mdc,mdc.mdc_desc "
				+ "  FROM DRGS.T_PATIENTDRG A, DRGS.t_Mdcgroup mdc WHERE  A.MDC = mdc.mdc_code and a.ver_code = mdc.ver_code "
				+ "  and A.PARDRG_CODE IN "
				+ "   (SELECT T.AS_DRG_CODE "
				+ "  FROM DRGS.T_ANNUALSTD T "
				+ "   WHERE T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ "    AND T.AS_RISK_LEVEL = '2') "
				+ "  AND EXISTS "
				+ "  (SELECT B.ID "
				+ "  FROM DRGS.T_MEDICALRECORD B "
				+ "  WHERE A.PARID = B.ID AND B.IN_DAYS < 61 "
				+ "  AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ "  ON E.ID = G.PARID "
				+ "  group by G.mdc having SUM(CASE WHEN E.OUT_WAY = '5' THEN 1  ELSE 0 END) > 0 order by caseDeadCount desc";
		List<Object[]> qualityMDCTableByRisk2List = getRepository()
				.createSqlQuery(sql).list();
		return qualityMDCTableByRisk2List;
	}

	/**
	 * 
	 * @Description:获取低风险死亡人数各科室分布情况表
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findQualityDepTableByRisk1List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		map.put("benchmarkYear", benchmarkYear);
		sql.append(" SELECT a.dep,a.incnt,a.deadcnt,a.ratio FROM (  \n");
		sql.append(" SELECT a.OUT_DEP dep ,sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='1'AND a.OUT_WAY='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(sum(case WHEN c.AS_RISK_LEVEL='1'AND a.OUT_WAY='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end),4)*100 as ratio \n");
		sql.append(" from drgs.t_medicalrecord a LEFT JOIN drgs.t_patientdrg b  ON a.id=b.PARID  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE a.IN_DAYS <61 and b.PARDRG_CODE is not NULL   \n");
		sql.append(" and c.AS_YEAR='" + benchmarkYear
				+ "' AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   \n");
		sql.append(" GROUP BY a.OUT_DEP ORDER BY deadcnt DESC   \n");
		sql.append(" )a WHERE a.incnt <>0 AND a.ratio >0  \n");
		List<Object[]> qualityDepTableByRisk1List = getRepository()
				.createSqlQuery(sql.toString()).list();
		return qualityDepTableByRisk1List;
	}

	/**
	 * 
	 * @methodname :findQualityDepTableByRisk2List
	 * @description :获取中低风险死亡人数各科室分布情况表
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findQualityDepTableByRisk2List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.dep,a.incnt,a.deadcnt,a.ratio FROM (  \n");
		sql.append(" SELECT a.OUT_DEP dep ,sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='2' AND a.OUT_WAY='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(sum(case WHEN c.AS_RISK_LEVEL='2'AND a.OUT_WAY='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end),4)*100 as ratio \n");
		sql.append(" from drgs.t_medicalrecord a LEFT JOIN drgs.t_patientdrg b  ON a.id=b.PARID  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE a.IN_DAYS <61 and b.PARDRG_CODE is not NULL   \n");
		sql.append(" and c.AS_YEAR='" + benchmarkYear
				+ "' AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   \n");
		sql.append(" GROUP BY a.OUT_DEP ORDER BY deadcnt DESC   \n");
		sql.append(" )a WHERE a.incnt <>0 AND a.ratio >0  \n");
		List<Object[]> qualityDepTableByRisk2List = getRepository()
				.createSqlQuery(sql.toString()).list();
		return qualityDepTableByRisk2List;
	}

	/**
	 * 
	 * @Description: 获取各个专业的病组覆盖率情况
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findMdcInfo4DRGsCoverRatio(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String sql = "select e.mdc,g.mdc_desc,e.drgsCount,g.drgTotalCount,e.drgsCount/g.drgTotalCount drgsRatio from  "
				+ " (SELECT a.mdc,count(distinct(a.pardrg_code)) drgsCount,a.ver_code FROM DRGS.T_PATIENTDRG a WHERE  "
				+ " a.pardrg_code is not null and exists (SELECT B.ID FROM DRGS.T_MEDICALRECORD B  WHERE A.PARID = B.ID   AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') group by a.mdc, a.ver_code) e  "
				+ " left join  ( "
				+ " select mg.mdc_code, count(drg.drg_code) drgTotalCount,mg.ver_code,mg.mdc_desc "
				+ " from DRGs.t_Drgs drg, DRGs.t_Adrg adrg, DRGs.t_Mdcgroup mg "
				+ " WHERE drg.adrg_code = adrg.adrg_code "
				+ " and adrg.mdc_code = mg.mdc_code "
				+ " and mg.ver_code = drg.ver_code  and drg.ver_code=adrg.ver_code "
				+ " group by mg.mdc_code,mg.ver_code,mg.mdc_desc "
				+ " ) g on e.mdc = g.mdc_code and e.ver_code=g.ver_code order by  drgsRatio";
		List<Object[]> mdcInfo4DRGsCoverRatioList = getRepository()
				.createSqlQuery(sql).list();
		return mdcInfo4DRGsCoverRatioList;
	}

	/**
	 * 
	 * @methodname :findMdcInfo4DRGsCoverRatio
	 * @description :获取缺失DRG病组权重大于1的专业列表
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findDefectDRGsTableWeightGreaterThan1(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCodeTopFive = paramMap == null ? "" : paramMap.get(
				"mdcCodeTopFive").toString();// 缺失专业
		String sql = "select g.as_mdc_code,g.as_drg_code,drg.drg_desc,g.as_weight from DRGS.T_DRGS drg right join  "
				+ " (SELECT t.as_drg_code,t.as_mdc_code,t.as_weight "
				+ " FROM DRGS.T_ANNUALSTD t "
				+ " WHERE t.as_year = '2014' and t.as_mdc_code in ("
				+ mdcCodeTopFive
				+ ") "
				+ " and not exists "
				+ " (SELECT a.pardrg_code FROM DRGS.T_PATIENTDRG a WHERE t.as_drg_code = a.pardrg_code and exists "
				+ " (SELECT B.ID FROM DRGS.T_MEDICALRECORD B "
				+ " WHERE A.PARID = B.ID AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) "
				+ " ) g on g.as_drg_code = drg.drg_code and drg.ver_code like '%V"
				+ benchmarkYear + "%' order by g.as_weight desc";
		List<Object[]> defectDRGsTableList = getRepository()
				.createSqlQuery(sql).list();
		return defectDRGsTableList;
	}

	/**
	 * 
	 * @methodname :findImage2CMIDRGsCount
	 * @description :获取图2信息：CMI和DRGs组数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findImage2CMIDRGsCount(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT b.out_dep_code,b.out_dep,count(a.parid),count(distinct(a.pardrg_code)) drgCount,SUM(T.AS_WEIGHT) / COUNT(A.PARID) cmiValue "
				+ " FROM DRGS.T_PATIENTDRG A  "
				+ " left join  DRGS.T_MEDICALRECORD B  on A.PARID = B.ID  "
				+ " left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ " WHERE  to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' "
				+ " group by b.out_dep_code, b.out_dep "
				+ " order by drgCount desc";
		List<Object[]> cmiDRGsCountList = getRepository().createSqlQuery(sql)
				.list();
		return cmiDRGsCountList;
	}

	public List<Object[]> findImage2CMIWeight(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT b.out_dep_code,b.out_dep,count(a.parid),SUM(T.AS_WEIGHT) drgCount,SUM(T.AS_WEIGHT) / COUNT(A.PARID) cmiValue "
				+ " FROM DRGS.T_PATIENTDRG A  "
				+ " left join  DRGS.T_MEDICALRECORD B  on A.PARID = B.ID  "
				+ " left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ " WHERE  to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' "
				+ " group by b.out_dep_code, b.out_dep "
				+ " order by drgCount desc";
		List<Object[]> cmiDRGsCountList = getRepository().createSqlQuery(sql)
				.list();
		return cmiDRGsCountList;
	}

	/**
	 * 
	 * @Description:费用消耗指数和时间消耗指数
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findImage3FeeAndTimeEff(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.MDC,G.MDC_DESC, SUM(E.COSTAMOUNT / G.AS_AVG_FEE) / COUNT(G.PARID) FEE, "
				+ " SUM(E.IN_DAYS / G.AS_AVG_DAYS) / COUNT(G.PARID) TIMEEFFe, COUNT(G.PARID) "
				+ " FROM DRGS.T_MEDICALRECORD E "
				+ " RIGHT JOIN (SELECT A.PARID, A.PARDRG_CODE, T.AS_AVG_FEE, T.AS_AVG_DAYS,A.MDC,MC.MDC_DESC "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T,DRGS.T_MDCGROUP MC "
				+ "  WHERE A.PARDRG_CODE = T.AS_DRG_CODE "
				+ " AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' AND A.MDC = MC.MDC_CODE AND MC.VER_CODE=A.VER_CODE "
				+ " AND EXISTS "
				+ " (SELECT B.ID "
				+ " FROM DRGS.T_MEDICALRECORD B "
				+ "  WHERE A.PARID = B.ID AND B.in_days <61 "
				+ " AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ " ON E.ID = G.PARID "
				+ " group by G.MDC,G.MDC_DESC ORDER BY TIMEEFFe DESC  ";
		List<Object[]> feeAndTimeEffList = getRepository().createSqlQuery(sql)
				.list();
		return feeAndTimeEffList;
	}

	/**
	 * 
	 * @Description:费用消耗指数
	 * @param paramMap
	 * @return
	 */
	public List<Object[]> findImage3Fee(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT G.MDC,G.MDC_DESC, SUM(E.COSTAMOUNT / G.AS_AVG_FEE) / COUNT(G.PARID) FEE, "
				+ " SUM(E.IN_DAYS / G.AS_AVG_DAYS) / COUNT(G.PARID) TIMEEFFe, COUNT(G.PARID) "
				+ " FROM DRGS.T_MEDICALRECORD E "
				+ " RIGHT JOIN (SELECT A.PARID, A.PARDRG_CODE, T.AS_AVG_FEE, T.AS_AVG_DAYS,A.MDC,MC.MDC_DESC "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T,DRGS.T_MDCGROUP MC "
				+ "  WHERE A.PARDRG_CODE = T.AS_DRG_CODE "
				+ " AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' AND A.MDC = MC.MDC_CODE AND MC.VER_CODE=A.VER_CODE "
				+ " AND EXISTS "
				+ " (SELECT B.ID "
				+ " FROM DRGS.T_MEDICALRECORD B "
				+ "  WHERE A.PARID = B.ID AND B.in_days <61 "
				+ " AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G "
				+ " ON E.ID = G.PARID "
				+ " group by G.MDC,G.MDC_DESC ORDER BY FEE DESC  ";
		List<Object[]> feeAndTimeEffList = getRepository().createSqlQuery(sql)
				.list();
		return feeAndTimeEffList;
	}

	/**
	 * 
	 * @methodname :findImage5MDCRisk1AndRisk2Count
	 * @description :获取图5信息：低风险和中低风险死亡人数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findImage5MDCRisk1AndRisk2Count(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT rt.* FROM "
				+ " (SELECT E.as_mdc_code,mg.mdc_desc,e.risk1caseDeadCount,e.risk2caseDeadCount from  "
				+ " (SELECT t.as_mdc_code, count(a.parid), "
				+ " SUM(CASE WHEN T.AS_RISK_LEVEL = '1'and B.OUT_WAY = '5' THEN 1  ELSE 0 END) risk1caseDeadCount, "
				+ " SUM(CASE WHEN T.AS_RISK_LEVEL = '2'and B.OUT_WAY = '5' THEN 1  ELSE 0 END) risk2caseDeadCount "
				+ " FROM DRGS.T_PATIENTDRG A  "
				+ " left join  DRGS.T_MEDICALRECORD B  on A.PARID = B.ID  "
				+ " left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ " WHERE  to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' and (T.AS_RISK_LEVEL = '1'or T.AS_RISK_LEVEL = '2') "
				+ " group by t.as_mdc_code) E left join DRGs.t_Mdcgroup mg on E.as_mdc_code ="
				+ " mg.mdc_code and mg.ver_code =(SELECT DISTINCT (p.ver_code) FROM drgs.t_patientdrg p "
				+ "LEFT JOIN drgs.t_medicalrecord mr ON mr.id=p.parid WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') "
				+ " ) rt  WHERE  (rt.risk1caseDeadCount > 0 or rt.risk2caseDeadCount > 0)";
		List<Object[]> mdcRisk1AndRisk2CountList = getRepository()
				.createSqlQuery(sql).list();
		return mdcRisk1AndRisk2CountList;
	}

	/**
	 * 
	 * @methodname :findImage6DepRisk1AndRisk2Count
	 * @description :获取图6信息：科室低风险和中低风险死亡人数
	 * @param :Map<String,Object> paramMap
	 * @author :liqingyang
	 * @return :List<Object[]>
	 * @date :2016年3月29日
	 */
	public List<Object[]> findImage6DepRisk1AndRisk2Count(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT rt.* FROM "
				+ " (SELECT B.Out_Dep_Code,B.Out_Dep, "
				+ " SUM(CASE WHEN T.AS_RISK_LEVEL = '1'and B.OUT_WAY = '5' THEN 1  ELSE 0 END) risk1caseDeadCount, "
				+ " SUM(CASE WHEN T.AS_RISK_LEVEL = '2'and B.OUT_WAY = '5' THEN 1  ELSE 0 END) risk2caseDeadCount "
				+ " FROM DRGS.T_PATIENTDRG A  "
				+ " left join  DRGS.T_MEDICALRECORD B  on A.PARID = B.ID  "
				+ " left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ " WHERE  to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' and (T.AS_RISK_LEVEL = '1'or T.AS_RISK_LEVEL = '2') "
				+ " group by B.Out_Dep_Code,B.Out_Dep "
				+ " ) rt  WHERE  (rt.risk1caseDeadCount > 0 or rt.risk2caseDeadCount > 0)";
		List<Object[]> depRisk1AndRisk2CountList = getRepository()
				.createSqlQuery(sql).list();
		return depRisk1AndRisk2CountList;
	}

	// word
	@Override
	public List<Map<String, Object>> findOperLevelInfo(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> parmap = new HashMap<String, Object>();
		
		parmap.put("benchmarkYear", benchmarkYear);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.oplevel, a.outCaeCnt,b.inCaseCnt,b.cmi,c.notinCaeCnt from ( \n");
		sql.append(" SELECT (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end) as oplevel,COUNT(mr.ID) outCaeCnt FROM drgs.t_medicalrecord mr ,drgs.t_operation o  \n");
		sql.append("  WHERE  mr.ID=o.PARID AND o.OPER_TYPE='M' AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  GROUP BY (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end) )a LEFT JOIN \n");
		sql.append(" (select (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end) as oplevel,count(p.PARID) inCaseCnt, ROUND(SUM(a.AS_WEIGHT)/count(p.PARID),2) cmi  FROM drgs.t_medicalrecord mr , \n");
		sql.append("  drgs.t_patientdrg  p ,drgs.t_operation o,drgs.t_annualstd a \n");
		sql.append("  WHERE  mr.ID=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   and p.PARDRG_CODE is not null  AND mr.IN_DAYS <61 \n");
		sql.append(" AND mr.ID=o.PARID  AND a.AS_YEAR=:benchmarkYear  AND o.OPER_TYPE='M' GROUP BY (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end) \n");
		sql.append(" ) b ON a.oplevel=b.oplevel LEFT JOIN \n");
		sql.append(" ( SELECT (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end) as oplevel,COUNT(mr.ID) notinCaeCnt FROM drgs.t_medicalrecord mr ,drgs.t_operation o ,drgs.t_patientdrg pd \n");
		sql.append(" WHERE  mr.ID=o.PARID and mr.id=pd.PARID AND o.OPER_TYPE='M'  and pd.PARDRG_CODE is NULL AND mr.IN_DAYS <61 \n");
		sql.append(" and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("  GROUP BY (CASE WHEN o.OPER_LEVEL is null THEN '无' else o.OPER_LEVEL end))c ON b.oplevel=c.oplevel \n");
		sql.append(" ORDER BY a.oplevel ASC\n");
		StringBuilder sqlHosp = new StringBuilder();
		sqlHosp.append(" SELECT a.outCaeCnt,b.inCaseCnt,b.cmi,c.notinCaeCnt from (\n");
		sqlHosp.append(" SELECT COUNT(O.PARID) outCaeCnt FROM drgs.t_medicalrecord mr , drgs.t_operation o \n");
		sqlHosp.append("  WHERE mr.ID=o.PARID AND o.OPER_TYPE='M'  \n");
		sqlHosp.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' )a, \n");
		sqlHosp.append(" (SELECT count(p.PARID) inCaseCnt, ROUND(SUM(a.AS_WEIGHT)/count(p.PARID),2) cmi  FROM drgs.t_medicalrecord mr ,\n");
		sqlHosp.append(" drgs.t_patientdrg  p ,drgs.t_operation o,drgs.t_annualstd a \n");
		sqlHosp.append(" WHERE  mr.ID=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE AND p.PARDRG_CODE is not null   \n");
		sqlHosp.append("  AND mr.ID=o.PARID  AND a.AS_YEAR=:benchmarkYear AND o.OPER_TYPE='M' AND MR.IN_DAYS <61 \n");
		sqlHosp.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' ) b ,\n");
		sqlHosp.append("(  SELECT COUNT(mr.ID) notinCaeCnt FROM drgs.t_medicalrecord mr ,drgs.t_operation o ,drgs.t_patientdrg pd  \n");
		sqlHosp.append("   WHERE  mr.ID=o.PARID and mr.id=pd.PARID AND o.OPER_TYPE='M'  and pd.PARDRG_CODE is NULL AND mr.IN_DAYS <61  \n");
		sqlHosp.append("   and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   )c\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.setParameters(parmap).list();
		Object[] hospData = getRepository().createSqlQuery(sqlHosp.toString())
				.setParameters(parmap).singleResult();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		// 全院手术
		Map<String, Object> hspmap = new HashMap<String, Object>();
		hspmap.put("operLevel", "全院手术");
		hspmap.put("caseCnt", hospData[0]);
		hspmap.put("caseCntRatio", 100);
		hspmap.put("inGroupCnt", hospData[1]);
		hspmap.put("CMI", hospData[2]);
		hspmap.put("notInGroupCnt", hospData[3]);
		resultList.add(hspmap);
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			String obj0 = obj[0] == null ? "0" : obj[0].toString().trim();
			Double caseCntRatio = Arith.div(Double.valueOf(obj[1] + "") * 100,
					Double.valueOf(hospData[0] + ""), 2);
			;
			if ("1".equals(obj0)) {
				map.put("operLevel", "一级");
				map.put("caseCnt", obj[1] == null ? "0" : obj[1]);
				map.put("caseCntRatio", caseCntRatio + "");
				map.put("inGroupCnt", obj[2] == null ? "0" : obj[2]);
				map.put("CMI", obj[3] == null ? "0" : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? "0" : obj[4]);
			} else if ("2".equals(obj0)) {
				map.put("operLevel", "二级");
				map.put("caseCnt", obj[1] == null ? "0" : obj[1]);
				map.put("caseCntRatio", caseCntRatio + "");
				map.put("inGroupCnt", obj[2] == null ? "0" : obj[2]);
				map.put("CMI", obj[3] == null ? "0" : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? "0" : obj[4]);
			} else if ("3".equals(obj0)) {
				map.put("operLevel", "三级");
				map.put("caseCnt", obj[1] == null ? "0" : obj[1]);
				map.put("caseCntRatio", caseCntRatio + "");
				map.put("inGroupCnt", obj[2] == null ? "0" : obj[2]);
				map.put("CMI", obj[3] == null ? "0" : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? "0" : obj[4]);
			} else if ("4".equals(obj0)) {
				map.put("operLevel", "四级");
				map.put("caseCnt", obj[1] == null ? "0" : obj[1]);
				map.put("caseCntRatio", caseCntRatio + "");
				map.put("inGroupCnt", obj[2] == null ? "0" : obj[2]);
				map.put("CMI", obj[3] == null ? "0" : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? "0" : obj[4]);
			} else {
				map.put("operLevel", "其他");
				map.put("caseCnt", obj[1] == null ? "0" : obj[1]);
				map.put("caseCntRatio", caseCntRatio + "");
				map.put("inGroupCnt", obj[2] == null ? "0" : obj[2]);
				map.put("CMI", obj[3] == null ? "0" : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? "0" : obj[4]);
			}

			resultList.add(map);
		}
		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.PdfRenderApplication#findOperDepInfoTop5
	 * (java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findOperDepInfoTop5(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();

		String sql = "SELECT O.OPER_CODE,O.OPERATION,O.OPER_LEVEL, COUNT(O. ID) FROM DRGS.T_MEDICALRECORD M LEFT JOIN DRGS.T_OPERATION O ON M . ID = O.PARID "
				+ "WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' "
				+ "AND O.OPER_TYPE = 'M' "
				+ "GROUP BY O.OPER_CODE,O.OPERATION,O.OPER_LEVEL ORDER BY COUNT(O. ID) DESC, O.OPER_CODE,O.OPERATION,O.OPER_LEVEL";
		List<Object[]> listTop5 = getRepository().createSqlQuery(sql).list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < 5; i++) {
			Object[] obj = listTop5.get(i);
			String sql2 = "SELECT M .OUT_DEP, COUNT(O. ID) FROM DRGS.T_MEDICALRECORD M LEFT JOIN DRGS.T_OPERATION O ON M . ID = O.PARID "
					+ "WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' "
					+ "AND O.OPER_TYPE = 'M' AND O.OPER_CODE = '"
					+ obj[0]
					+ "' AND O.OPER_LEVEL = '"
					+ obj[2]
					+ "' "
					+ "GROUP BY M .OUT_DEP ORDER BY COUNT(O. ID) DESC";
			List<Object[]> operDepList = getRepository().createSqlQuery(sql2)
					.list();
			int top5Cnt = 0;
			for (int j = 0; j < operDepList.size(); j++) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("order", i + 1);
				map.put("operCode", obj[0]);
				map.put("operDesc", obj[1]);
				map.put("operLevel", obj[2]);
				map.put("depName", operDepList.get(j)[0]);
				map.put("caseCnt", operDepList.get(j)[1]);
				map.put("caseCntRatio", Arith.div(
						Double.valueOf(operDepList.get(j)[1] + "") * 100,
						Double.valueOf(obj[3] + ""), 2));
				top5Cnt += Integer.valueOf(operDepList.get(j)[1] + "");

				if (j == 6) {
					map.put("depName", "其他");
					map.put("caseCnt", Integer.valueOf(obj[3] + "") - top5Cnt);
					map.put("caseCntRatio", Arith.div(
							(Double.valueOf(obj[3] + "") - top5Cnt) * 100,
							Double.valueOf(obj[3] + ""), 2));
				}
				resultList.add(map);
			}
		}

		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.PdfRenderApplication#findDicDRGsCnt(java
	 * .util.Map)
	 */
	@Override
	public String findDicDRGsCnt(Map<String, Object> paramMap) {
		String groupVersion = paramMap == null ? "" : paramMap.get(
				"groupVersion").toString();
		String sql = "SELECT COUNT(*) FROM DRGS.T_DRGS D WHERE D .VER_CODE IN "
				+ "(select t.VER_CODE from (SELECT  V.VER_CODE FROM DRGS.T_DRGVERSION V  WHERE V.VER_DESC = '"
				+ groupVersion + "' and  rownum < 2 )as t  )";
		Object drgNum = getRepository().createSqlQuery(sql).singleResult();
		Integer drgNumo = Integer.parseInt(drgNum.toString());
		return drgNumo + "";
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.PdfRenderApplication#findHospDRGsInfo(java
	 * .util.Map)
	 */
	@Override
	public List<Map<String, Object>> findHospDRGsInfo(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT P .PARDRG_CODE FROM DRGS.T_MEDICALRECORD M  \n ");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M . ID = P .PARID  \n ");
		sql.append("  WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'\n ");
		sql.append("  and P .PARDRG_CODE is not null and m.in_days <61 \n ");
		List<String> list = getRepository().createSqlQuery(sql.toString())
				.list();

		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (String obj : list) {
			Map<String, Object> mapo = new HashMap<String, Object>();
			mapo.put("drgCode", obj);
			resultList.add(mapo);
		}
		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.PdfRenderApplication#findDepWeightAndCMI
	 * (java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findDepWeightAndCMI(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> Pmap = new HashMap<String, Object>();
		
		Pmap.put("benchmarkYear", benchmarkYear);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT M.OUT_DEP, ROUND(SUM(A.AS_WEIGHT),2),ROUND(SUM(A .AS_WEIGHT) / COUNT(P.PARID), 2 ) \n");
		sql.append(" FROM DRGS.T_MEDICALRECORD M LEFT JOIN DRGS.T_PATIENTDRG P ON M .ID = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND A.AS_YEAR =:benchmarkYear AND A.AS_WEIGHT  IS not NULL and m.in_days <61 and P.PARDRG_CODE is not null GROUP BY M.OUT_DEP \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.setParameters(Pmap).list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("depname", obj[0]);
			map.put("weight", obj[1] == null ? 0 : obj[1]);
			map.put("cmi", obj[2] == null ? 0 : obj[2]);
			resultList.add(map);
		}
		return resultList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.PdfRenderApplication#findMdcWeightAndCMI
	 * (java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findMdcWeightAndCMI(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT P.MDC,MDC.MDC_DESC, SUM(A.AS_WEIGHT), ROUND (SUM(A.AS_WEIGHT) / COUNT(P.PARID), 2 ) \n");
		sql.append(" FROM DRGS.T_MEDICALRECORD M LEFT JOIN DRGS.T_PATIENTDRG P ON M.ID = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A ON P.PARDRG_CODE = A.AS_DRG_CODE  \n");
		sql.append(" LEFT JOIN DRGS.T_MDCGROUP mdc on mdc.mdc_code=p.mdc \n");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND A.AS_YEAR = '" + benchmarkYear
				+ "' GROUP BY P.MDC,MDC.MDC_DESC  \n");

		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("mdcCode", obj[0]);
			map.put("mdcDesc", obj[1]);
			map.put("weight", obj[2]);
			map.put("CMI", obj[3]);
			resultList.add(map);
		}

		return resultList;
	}

	@Override
	public List<Map<String, Object>> finddrgsDepCntRisk1(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();

		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.OUT_DEP ,a.counta,b.countb,b.countb/a.counta FROM( \n ");
		sql.append(" SELECT mr.OUT_DEP, COUNT(1) counta FROM drgs.t_medicalrecord mr LEFT JOIN drgs.t_patientdrg p ON mr.id=p.PARID  \n ");
		sql.append(" LEFT JOIN drgs.t_annualstd an ON p.PARDRG_CODE = an.AS_DRG_CODE where an.AS_RISK_LEVEL ='1' AND p.PARDRG_CODE IS NOT null \n ");
		sql.append(" AND mr.IN_DAYS <61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("  AND an.AS_YEAR = '" + benchmarkYear
				+ "'  GROUP BY mr.OUT_DEP ) a RIGHT  JOIN (  \n ");
		sql.append(" SELECT mr.OUT_DEP, COUNT(1) countb FROM drgs.t_medicalrecord mr LEFT JOIN drgs.t_patientdrg p ON mr.id=p.PARID  \n ");
		sql.append(" LEFT JOIN drgs.t_annualstd an ON p.PARDRG_CODE = an.AS_DRG_CODE where an.AS_RISK_LEVEL ='1' AND mr.OUT_WAY= '5' AND p.PARDRG_CODE IS NOT null AND mr.IN_DAYS <61 \n ");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("  AND an.AS_YEAR = '"
				+ benchmarkYear
				+ "'  GROUP BY mr.OUT_DEP )b ON a.OUT_DEP=b.OUT_DEP AND b.countb/a.counta >0  ORDER BY (b.countb/a.counta ) DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = new HashMap<String, Object>();
			Object[] obj = list.get(i);
			map.put("order", i + 1);
			map.put("dep", obj[0]);
			map.put("inRisk1Cnt", obj[1]);
			map.put("inRisk1DiedCnt", obj[2]);
			map.put("inRisk1DiedRate", Double.valueOf(obj[3] + "") * 100);
			resultList.add(map);
		}
		return resultList;
	}

	@Override
	public List<Map<String, Object>> finddrgsDepCntRisk2(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.OUT_DEP ,a.counta,b.countb,b.countb/a.counta FROM( \n ");
		sql.append(" SELECT mr.OUT_DEP, COUNT(1) counta FROM drgs.t_medicalrecord mr LEFT JOIN drgs.t_patientdrg p ON mr.id=p.PARID  \n ");
		sql.append(" LEFT JOIN drgs.t_annualstd an ON p.PARDRG_CODE = an.AS_DRG_CODE where an.AS_RISK_LEVEL ='2' AND p.PARDRG_CODE IS NOT null \n ");
		sql.append(" AND mr.IN_DAYS <61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("  AND an.AS_YEAR = '" + benchmarkYear
				+ "'  GROUP BY mr.OUT_DEP ) a RIGHT  JOIN (  \n ");
		sql.append(" SELECT mr.OUT_DEP, COUNT(1) countb FROM drgs.t_medicalrecord mr LEFT JOIN drgs.t_patientdrg p ON mr.id=p.PARID  \n ");
		sql.append(" LEFT JOIN drgs.t_annualstd an ON p.PARDRG_CODE = an.AS_DRG_CODE where an.AS_RISK_LEVEL ='2' AND mr.OUT_WAY= '5' AND p.PARDRG_CODE IS NOT null AND mr.IN_DAYS <61 \n ");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("  AND an.AS_YEAR = '"
				+ benchmarkYear
				+ "'  GROUP BY mr.OUT_DEP )b ON a.OUT_DEP=b.OUT_DEP AND b.countb/a.counta >0  ORDER BY (b.countb/a.counta ) DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = new HashMap<String, Object>();
			Object[] obj = list.get(i);
			map.put("order", i + 1);
			map.put("dep", obj[0]);
			map.put("inRisk2Cnt", obj[1]);
			map.put("inRisk2DiedCnt", obj[2]);
			map.put("inRisk2DiedRate", Double.valueOf(obj[3] + "") * 100);
			resultList.add(map);
		}
		return resultList;
	}

	@Override
	public List<Object[]> findCapacityDepByTotalWeight(
			Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String sql = "SELECT b.out_dep_code, b.out_dep, SUM(T.AS_WEIGHT)  totalweigh "
				+ " FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGS.T_MEDICALRECORD B "
				+ " WHERE A.PARDRG_CODE = T.AS_DRG_CODE "
				+ " AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' "
				+ " AND A.PARID = B.ID "
				+ " AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  AND A.PARDRG_CODE IS NOT NULL "
				+ " group by b.out_dep_code, b.out_dep order by totalweigh desc";
		List<Object[]> depTableInfoByTotalWeightList = getRepository()
				.createSqlQuery(sql).list();

		return depTableInfoByTotalWeightList;
	}

	@Override
	public String getDRGversion(Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		String drgsVersion = "2014北京版";
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT VER_DESC,VER_CODE FROM drgs.t_drgversion v WHERE v.VER_CODE= \n ");
		sql.append("  (SELECT p.VER_CODE from drgs.t_patientdrg p LEFT JOIN drgs.t_medicalrecord m  ");
		sql.append("   ON m.id=p.parid WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' and  rownum < 2) \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		if (list.size() > 0) {
			Object[] obj = list.get(0);
			drgsVersion = obj[0].toString();
		}
		return drgsVersion;
	}

	@Override
	public Double gettotalWeigh(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT sum(x.AS_WEIGHT *y.mrcnt)  FROM( \n ");
		sql.append(" SELECT a.AS_DRG_CODE,a.AS_WEIGHT FROM drgs.t_annualstd a WHERE a.AS_YEAR = '"
				+ benchmarkYear + "' \n ");
		sql.append(" ) x LEFT  JOIN ( SELECT p.PARDRG_CODE, count(m.id) mrcnt 	FROM drgs.t_medicalrecord m \n ");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON m.ID = p.PARID 	WHERE p.PARDRG_CODE IS NOT NULL AND m.IN_DAYS <61 \n ");
		sql.append(" AND to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append(" GROUP BY p.PARDRG_CODE ) y ON x.AS_DRG_CODE = y.PARDRG_CODE \n ");
		sql.append(" WHERE y.PARDRG_CODE is NOT NULL \n ");
		Object drgNum = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		Double drgCnt = Double.valueOf(drgNum.toString());
		return drgCnt;
	}

	@Override
	public List<Object[]> getBenchmarkYear() {
		// TODO Auto-generated method stub
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT DISTINCT(a.AS_YEAR) FROM drgs.t_annualstd a ORDER BY a.AS_YEAR DESC \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMainOperList(Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT o.OPER_CODE,COUNT(m.ID) cnt FROM drgs.t_operation o \n ");
		sql.append("  LEFT   JOIN drgs.t_medicalrecord m \n ");
		sql.append(" ON o.PARID=m.ID LEFT JOIN drgs.t_patientdrg p 	ON m.ID=p.PARID\n ");
		sql.append(" WHERE o.OPER_TYPE='M' \n ");
		sql.append("  AND to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  ");
		sql.append("GROUP BY o.OPER_CODE  ORDER BY cnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findTop20ByLevelList(Map<String, Object> paramMap,
			String sqlstr) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT o.OPER_LEVEL,COUNT(o.OPER_CODE) levelcnt FROM drgs.t_operation o  \n ");
		sql.append("  LEFT JOIN drgs.t_medicalrecord m ON m.ID=o.PARID \n ");
		sql.append("  where to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  ");
		sql.append("  AND o.OPER_CODE in " + sqlstr
				+ " AND o.OPER_TYPE='M'  \n");
		sql.append(" GROUP BY o.OPER_LEVEL ORDER BY levelcnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> mainOperTop5ByLevelList(Map<String, Object> paramMap,
			String level) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT o.OPER_LEVEL,o.OPER_CODE,o.OPERATION,COUNT(o.OPER_CODE) cnt \n");
		sql.append(" FROM DRGS.T_MEDICALRECORD M \n");
		sql.append(" LEFT JOIN DRGS.T_OPERATION O ON M.ID = O.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M.ID = P.PARID \n");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND O.OPER_TYPE = 'M' AND O.OPER_LEVEL='" + level + "' \n");
		sql.append("GROUP BY o.OPER_LEVEL,o.OPER_CODE,o.OPERATION ORDER BY OPER_LEVEL  , cnt DESC limit 5 ");

		List<Object[]> top5list = getRepository()
				.createSqlQuery(sql.toString()).list();
		return top5list;
	}

	@Override
	public List findLevelNamelList(Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sqllevel = new StringBuilder();
		sqllevel.append(" SELECT DISTINCT(o.OPER_LEVEL) levelname \n");
		sqllevel.append(" FROM DRGS.T_MEDICALRECORD M \n");
		sqllevel.append(" LEFT JOIN DRGS.T_OPERATION O ON M.ID = O.PARID \n");
		sqllevel.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M.ID = P.PARID \n");
		sqllevel.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sqllevel.append(" AND O.OPER_TYPE = 'M' \n ");
		sqllevel.append(" ORDER BY levelname  ASC ");
		List levlist = getRepository().createSqlQuery(sqllevel.toString())
				.list();
		return levlist;
	}

	@Override
	public int findLevelCnt(Map<String, Object> paramMap, String levelname) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT count(O.parid) \n");
		sql.append(" FROM DRGS.T_MEDICALRECORD M \n");
		sql.append(" LEFT JOIN DRGS.T_OPERATION O ON M.ID = O.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M.ID = P.PARID \n");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND O.OPER_TYPE = 'M' \n ");
		sql.append(" AND O.OPER_LEVEL='" + levelname + "' ");
		Object levelNum = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int levelCnt = Integer.valueOf(levelNum.toString());
		return levelCnt;
	}

	@Override
	public List<Object[]> findOver60ByDepList(Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT M.OUT_DEP,COUNT(M.ID) cnt FROM drgs.t_medicalrecord M  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON M.id=p.parid \n");
		sql.append(" WHERE M.IN_DAYS >60 \n");
		sql.append(" and  to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY M.OUT_DEP ORDER BY cnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findnotinGroupByDepList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT M.OUT_DEP,COUNT(M.ID) cnt FROM drgs.t_medicalrecord M  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON M.id=p.parid \n");
		sql.append(" WHERE M.IN_DAYS <61 and p.PARDRG_CODE is NULL \n");
		sql.append(" and  to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY M.OUT_DEP ORDER BY cnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findCmiDrgscntList(Map<String, Object> paramMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT cmi,drgcnt,hospname from drgs.t_allhsp\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findFeeTimeList(Map<String, Object> paramMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT feeEff,timeEff,hospname from drgs.t_allhsp\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public int findOutdepCnt(Map<String, Object> paramMap, String outdepsqla) {
		// TODO Auto-generated method stub
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(p.parid) FROM drgs.t_medicalrecord m LEFT JOIN \n");
		sql.append(" drgs.t_patientdrg p ON m.ID=p.PARID  ");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND p.PARDRG_CODE in(" + outdepsqla + ") \n ");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int count = Integer.valueOf(cnt.toString());
		return count;
	}

	@Override
	public int finIndepCnt(Map<String, Object> paramMap, String indepsqla) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(p.parid) FROM drgs.t_medicalrecord m LEFT JOIN \n");
		sql.append(" drgs.t_patientdrg p ON m.ID=p.PARID  ");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND p.PARDRG_CODE in(" + indepsqla + ") \n ");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int count = Integer.valueOf(cnt.toString());
		return count;
	}

	@Override
	public int findOperdepCnt(Map<String, Object> paramMap, String opersqla) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(p.parid) FROM drgs.t_medicalrecord m LEFT JOIN \n");
		sql.append(" drgs.t_patientdrg p ON m.ID=p.PARID  ");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND p.PARDRG_CODE in(" + opersqla + ") \n ");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int count = Integer.valueOf(cnt.toString());
		return count;
	}

	public Object[] findSimpHospBaseTable2(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("reportDateFrom", reportDateFrom);
		map.put("reportDateTo", reportDateTo);

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.allcnt,ROUND(a.COSTAMOUNT/a.allcnt,2),ROUND(a.dayscnt/a.allcnt,2), \n");
		sql.append(" ROUND(a.DRUGCOST*100/a.COSTAMOUNT,2) ,ROUND(a.CASEDEAD*100/a.allcnt,2)FROM ( \n");
		sql.append(" SELECT COUNT(mr.ID) allcnt ,SUM(mr.IN_DAYS) dayscnt ,sum(mr.COSTAMOUNT) COSTAMOUNT,\n");
		sql.append(" sum(mr.DRUGCOST) DRUGCOST ,SUM(CASE WHEN mr.OUT_WAY = 5 THEN 1 ELSE 0 END) CASEDEAD \n");
		sql.append(" FROM drgs.t_medicalrecord mr where  \n");
		sql.append(" to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')  a\n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return hospBaseQuota;
	}

	public Object[] findSimpHospBaseTable4(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT(p.PARDRG_CODE)),ROUND(SUM(a.AS_WEIGHT)/COUNT(p.PARID),2) ,SUM(a.AS_WEIGHT)totalweight,\n");
		sql.append("  COUNT(p.PARID) casecnt ,round(sum(mr.COSTAMOUNT/a.AS_AVG_FEE)/COUNT(p.parid),2) as effee, \n");
		sql.append("  round(sum(mr.in_days/a.AS_AVG_DAYS)/COUNT(p.parid),2) as timefee, \n");
		sql.append("  round(sum(case when mr.OUT_WAY='5' and a.AS_RISK_LEVEL='1' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END)*100,2) \n");
		sql.append("  FROM drgs.t_medicalrecord mr LEFT JOIN  drgs.t_patientdrg p ON mr.ID=p.PARID LEFT JOIN   drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("  WHERE p.PARDRG_CODE is not null AND a.AS_YEAR=:benchmarkYear and mr.in_days <61  and a.as_weight is not null  \n");
		sql.append("  and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).singleResult();
		return hospBaseQuota;
	}

	@Override
	public List<Object[]> findmainOperLevelInfo(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT b.OPER_LEVEL, COUNT(B.PARID) CNT FROM drgs.t_medicalrecord a LEFT JOIN drgs.t_operation b on a.id=b.PARID \n ");
		sql.append("  WHERE b.OPER_TYPE='M' AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("  GROUP BY b.OPER_LEVEL ORDER BY b.OPER_LEVEL ASC \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMainOperTop5DisList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append("	SELECT o.OPER_CODE,O.OPERATION,O.OPER_LEVEL FROM drgs.t_operation o,  \n ");
		sql.append(" 	drgs.t_medicalrecord mr  \n ");
		sql.append("    WHERE  mr.ID=o.PARID AND o.OPER_CODE <>'NA'and o.oper_type='M' and rownum < 6 \n ");
		sql.append("    AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n ");
		sql.append("    GROUP BY o.OPER_CODE,O.OPERATION,O.OPER_LEVEL \n ");
		sql.append("   	ORDER BY COUNT(o.OPER_CODE)  DESC   \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Map<String, Object>> findMainOperTop5List(
			Map<String, Object> paramMap, Object opercode, int n) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT o.OPER_CODE,o.operation,o.OPER_LEVEL,mr.OUT_DEP,COUNT(o.OPER_CODE) caseCnt FROM  \n ");
		sql.append("  drgs.t_medicalrecord mr ,drgs.t_operation o \n ");
		sql.append("  WHERE mr.ID=o.PARID AND o.OPER_CODE ='" + opercode
				+ "' and o.oper_type='M'\n ");
		sql.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY o.OPER_CODE,o.operation,o.OPER_LEVEL,mr.OUT_DEP \n ");
		sql.append(" ORDER BY caseCnt DESC  \n ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		List<Map<String, Object>> resultlist = new ArrayList<Map<String, Object>>();
		int totalcnt = 0;
		for (Object[] objtotal : list) {
			totalcnt = totalcnt + Integer.valueOf(objtotal[4].toString());
		}
		int order = 1;
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("order", n);
			map.put("opercode", obj[0]);
			map.put("operation", obj[1]);
			if (obj[2] != null && obj[2].toString().trim().equals("1")) {
				map.put("operlevel", "一级");
			} else if (obj[2] != null && obj[2].toString().trim().equals("2")) {
				map.put("operlevel", "二级");
			} else if (obj[2] != null && obj[2].toString().trim().equals("3")) {
				map.put("operlevel", "三级");
			} else if (obj[2] != null && obj[2].toString().trim().equals("4")) {
				map.put("operlevel", "四级");
			} else {
				map.put("operlevel", "无级");
			}
			map.put("outdep", obj[3]);
			map.put("opercnt", obj[4]);
			map.put("operatio", Arith.mul(
					Arith.div(Double.valueOf(obj[4].toString()), totalcnt, 4),
					100));
			resultlist.add(map);
			order++;
		}
		Map<String, Object> maporder = new HashMap<String, Object>();
		maporder.put("order", order);// 用来合并手术编码，手术单元格的参数
		resultlist.add(maporder);
		return resultlist;
	}

	@Override
	public int findPersonCnt(Map<String, Object> paramMap, Object object) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(p.parid) FROM drgs.t_medicalrecord m LEFT JOIN \n");
		sql.append(" drgs.t_patientdrg p ON m.ID=p.PARID  ");
		sql.append(" WHERE to_char(m.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND p.PARDRG_CODE ='" + object
				+ "' and m.in_days < 61 \n ");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int count = Integer.valueOf(cnt.toString());
		return count;
	}

	@Override
	public int findDepCount() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT COUNT(1) FROM drgs.t_department \n");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int count = Integer.valueOf(cnt.toString());
		return count;
	}

	@Override
	public List<Object[]> findmdcBymdcCodeList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.*,b.* FROM ( \n");
		sql.append(" SELECT m.MDC_DESC ,count(p.PARID) ,COUNT(DISTINCT(p.PARDRG_CODE)) from drgs.t_medicalrecord mr,drgs.t_mdcgroup m , \n");
		sql.append(" drgs.t_patientdrg p,drgs.t_drgs drg \n");
		sql.append(" WHERE mr.ID=p.PARID AND p.MDC=m.MDC_CODE AND p.PARDRG_CODE=drg.DRG_CODE  \n");
		sql.append(" AND mr.IN_DAYS <61 AND p.PARDRG_CODE is not NULL  AND m.MDC_code in ("
				+ mdcCode + ") \n");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND  P.VER_CODE= M.VER_CODE AND p.VER_CODE=drg.VER_CODE 	)a, \n");
		sql.append("(SELECT count(DISTINCT(d.drg_code)) FROM drgs.t_drgs d ,drgs.t_adrg a ,drgs.t_mdcgroup m \n");
		sql.append(" WHERE d.adrg_code=a.adrg_code  AND a.MDC_CODE=m.MDC_CODE AND m.MDC_CODE in ("
				+ mdcCode + ") \n");
		sql.append(" AND d.ver_code =( SELECT DISTINCT (p.ver_code) FROM drgs.t_patientdrg p \n");
		sql.append(" LEFT JOIN drgs.t_medicalrecord mr ON mr.id=p.parid \n");
		sql.append(" WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'))b \n");
		List<Object[]> mdclist = getRepository().createSqlQuery(sql.toString())
				.list();
		return mdclist;
	}

	@Override
	public List<Object[]> finddrgsByMdcCodeTop10List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT d.DRG_CODE,d.DRG_DESC,COUNT(p.PARID),a.AS_WEIGHT  \n");
		sql.append(" FROM drgs.t_medicalrecord mr RIGHT  JOIN \n");
		sql.append(" drgs.t_patientdrg p ON mr.ID =p.PARID\n");
		sql.append("LEFT JOIN drgs.t_drgs d ON p.PARDRG_CODE=d.DRG_CODE\n");
		sql.append("LEFT JOIN drgs.t_annualstd a ON d.DRG_CODE=a.AS_DRG_CODE \n");
		sql.append("WHERE p.MDC in (" + mdcCode + ")  AND a.AS_YEAR='"
				+ benchmarkYear
				+ "' and a.as_weight is not null AND d.VER_CODE= \n");
		sql.append(" (SELECT DISTINCT (p.ver_code) FROM drgs.t_patientdrg p \n");
		sql.append(" LEFT JOIN drgs.t_medicalrecord mr ON mr.id=p.parid \n");
		sql.append(" WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') \n");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY d.DRG_CODE,d.DRG_DESC ORDER BY COUNT(p.PARID) DESC \n");
		List<Object[]> mdclist = getRepository().createSqlQuery(sql.toString())
				.list();
		return mdclist;

	}

	@Override
	public List<Object[]> finddrgsTotalWeightByMdcCodeTop10List(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT d.DRG_CODE,d.DRG_DESC,COUNT(p.PARID),a.AS_WEIGHT,COUNT(p.PARID)*a.AS_WEIGHT as totalWeight \n");
		sql.append(" FROM drgs.t_medicalrecord mr RIGHT  JOIN \n");
		sql.append(" drgs.t_patientdrg p ON mr.ID =p.PARID\n");
		sql.append("LEFT JOIN drgs.t_drgs d ON p.PARDRG_CODE=d.DRG_CODE\n");
		sql.append("LEFT JOIN drgs.t_annualstd a ON d.DRG_CODE=a.AS_DRG_CODE \n");
		sql.append("WHERE p.MDC in (" + mdcCode + ")  AND a.AS_YEAR='"
				+ benchmarkYear
				+ "'  and a.as_weight is not null AND d.VER_CODE= \n");
		sql.append(" (SELECT DISTINCT (p.ver_code) FROM drgs.t_patientdrg p \n");
		sql.append(" LEFT JOIN drgs.t_medicalrecord mr ON mr.id=p.parid \n");
		sql.append(" WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') \n");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY d.DRG_CODE,d.DRG_DESC ORDER BY totalWeight  DESC \n");
		List<Object[]> mdclist = getRepository().createSqlQuery(sql.toString())
				.list();
		return mdclist;
	}

	public Object[] findSimpMdcBaseTable4(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();

		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(a.totalweight),SUM(casecnt),ROUND(SUM(a.totalweight)/SUM(casecnt),2), b.drgsCnt  FROM ( \n");
		sql.append(" SELECT COUNT(p.PARDRG_CODE)*a.AS_WEIGHT totalweight, \n");
		sql.append(" COUNT(p.PARID) casecnt \n");
		sql.append(" FROM drgs.t_medicalrecord mr LEFT JOIN  \n");
		sql.append(" drgs.t_patientdrg p ON mr.ID=p.PARID LEFT JOIN \n");
		sql.append(" drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append(" WHERE p.PARDRG_CODE is not null AND a.AS_YEAR='"
				+ benchmarkYear
				+ "' and a.as_weight is not null and p.PARDRG_CODE is not null and mr.in_days <61 and p.mdc in ("
				+ mdcCode + ") \n");
		sql.append(" and  to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY p.PARDRG_CODE) a , \n");
		sql.append(" ( SELECT COUNT(DISTINCT(p.PARDRG_CODE)) drgsCnt  FROM drgs.t_patientdrg p\n");
		sql.append(" LEFT JOIN drgs.t_medicalrecord mr ON p.parid=mr.ID WHERE p.PARDRG_CODE IS NOT NULL and mr.in_days <61 and  p.mdc in ("
				+ mdcCode + ")  \n");
		sql.append("  and  to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') b \n");
		Object[] hospBaseQuota = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return hospBaseQuota;
	}

	public Object[] findSimpMdcBaseTable4feetime(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		String sql = "SELECT round(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2),round(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2) FROM  DRGS.T_MEDICALRECORD E RIGHT JOIN (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T WHERE A.PARDRG_CODE = T.AS_DRG_CODE and a.mdc in ("
				+ mdcCode
				+ ")  AND T.AS_YEAR = '"
				+ benchmarkYear
				+ "' and t.as_weight is not null AND EXISTS (SELECT B.ID FROM DRGS.T_MEDICALRECORD B WHERE A.PARID = B.ID AND  B.IN_DAYS  <61 AND A.PARDRG_CODE IS NOT NULL AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')) G  ON E.ID = G.PARID ";
		Object[] hospCaseFeeEffAndTimeEff = getRepository().createSqlQuery(sql)
				.singleResult();
		return hospCaseFeeEffAndTimeEff;
	}

	public double findMdcCaseRisk1Ratio(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(p.PARID),SUM(CASE WHEN mr.OUT_WAY='5' then 1 else 0 END )  \n");
		sql.append("FROM drgs.t_medicalrecord mr  \n");
		sql.append("LEFT JOIN drgs.t_patientdrg p ON mr.ID =p.PARID  \n");
		sql.append("LEFT JOIN drgs.t_annualstd a on p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("WHERE p.PARDRG_CODE is NOT null AND mr.IN_DAYS < 61\n");
		sql.append("AND a.AS_RISK_LEVEL='1' AND p.MDC in (" + mdcCode
				+ ") AND a.AS_YEAR='" + benchmarkYear + "'\n");
		Object[] obj = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		double hospCaseRisk1Ratio = 0;
		if (obj[0] != null && obj[0] != "" && obj[1] != null && obj[1] != "") {
			if (Double.valueOf(obj[0].toString()).intValue() != 0
					&& Double.valueOf(obj[1].toString()).intValue() != 0) {
				hospCaseRisk1Ratio = Arith.div(Double
						.valueOf(obj[1].toString()).intValue(),
						Double.valueOf(obj[0].toString()).intValue(), 4);
			}
		}
		return hospCaseRisk1Ratio;
	}

	public double findMdcCaseRisk2Ratio(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(p.PARID),SUM(CASE WHEN mr.OUT_WAY='5' then 1 else 0 END )  \n");
		sql.append("FROM drgs.t_medicalrecord mr  \n");
		sql.append("LEFT JOIN drgs.t_patientdrg p ON mr.ID =p.PARID   \n");
		sql.append("LEFT JOIN drgs.t_annualstd a on p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("WHERE p.PARDRG_CODE is NOT null AND mr.IN_DAYS < 61\n");
		sql.append("AND a.AS_RISK_LEVEL='2' AND p.MDC in (" + mdcCode
				+ ") AND a.AS_YEAR='" + benchmarkYear + "'\n");
		Object[] obj = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		double hospCaseRisk2Ratio = 0;
		if (obj[0] != null && obj[0] != "" && obj[1] != null && obj[1] != "") {
			if (Double.valueOf(obj[0].toString()).intValue() != 0
					&& Double.valueOf(obj[1].toString()).intValue() != 0) {
				hospCaseRisk2Ratio = Arith.div(Double
						.valueOf(obj[1].toString()).intValue(),
						Double.valueOf(obj[0].toString()).intValue(), 4);
			}
		}
		return hospCaseRisk2Ratio;
	}

	@Override
	public List<Object[]> findDepOver60List(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT mr.OUT_DEP,COUNT(mr.ID) casecnt FROM drgs.t_medicalrecord mr \n");
		sql.append(" WHERE mr.IN_DAYS >60 AND\n");
		sql.append(" to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY mr.OUT_DEP ORDER BY casecnt desc \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findNotInGroupReason(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT g.NOTIN_REASON ,COUNT(G.MR_ADM) FROM drgs.t_medicalrecord mr  \n");
		sql.append(" LEFT JOIN drgs.t_groupinglog g ON mr.id=g.MR_ADM \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.ID=p.PARID\n");
		sql.append(" where  mr.IN_DAYS<61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" and p.PARDRG_CODE is NULL AND  g.NOTIN_REASON <>0 GROUP BY g.NOTIN_REASON \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findNotInGroupCaseByDepList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT mr.OUT_DEP,COUNT(mr.ID) notInCaeCnt FROM drgs.t_medicalrecord mr  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.ID=p.PARID \n");
		sql.append(" where  mr.IN_DAYS<61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" and  p.PARDRG_CODE IS NULL GROUP BY mr.OUT_DEP ORDER BY notInCaeCnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findNotInGroupCaseByDiagList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT mr.DIAGNOSE_CODE_MAIN ,mr.DIAGNOSE_DESS_MAIN,COUNT(mr.ID) notInCaeCnt FROM drgs.t_medicalrecord mr  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.ID=p.PARID \n");
		sql.append(" where  mr.IN_DAYS<61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" and rownum < 6  and  p.PARDRG_CODE IS NULL AND mr.DIAGNOSE_CODE_MAIN IS NOT NULL  GROUP BY mr.DIAGNOSE_CODE_MAIN ,mr.DIAGNOSE_DESS_MAIN ORDER BY COUNT(mr.ID)  DESC     \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findNotInGroupCaseByOperList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT o.OPER_CODE ,o.OPERATION,COUNT(mr.ID) notInCaeCnt FROM drgs.t_medicalrecord mr  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.ID=p.PARID \n");
		sql.append(" LEFT JOIN drgs.t_operation o ON mr.ID=o.PARID \n");
		sql.append(" where  mr.IN_DAYS<61 AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" and  p.PARDRG_CODE IS NULL AND o.OPER_TYPE='M' AND MR.IN_DAYS <61 AND o.OPER_CODE IS NOT NULL    AND  rownum < 6 GROUP BY o.OPER_CODE ,o.OPERATION  ORDER BY COUNT(mr.ID) DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMdcDrgsTop5List(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT x.MDC,round(x.CNT/y.CNT,2) as ratio FROM (  \n");
		sql.append("select MDC ,count(DISTINCT p.PARDRG_CODE) AS CNT FROM drgs.t_patientdrg p, drgs.t_medicalrecord mr  \n");
		sql.append("where p.PARDRG_CODE is not NULL AND p.PARID=mr.ID  AND  \n");
		sql.append(" to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("GROUP BY p.MDC ) x LEFT JOIN  \n");
		sql.append("(SELECT  ('MDC' ||  substr(a.DRG_CODE, 0,1)) AS MDC ,COUNT(DISTINCT A.DRG_CODE) AS CNT   \n");
		sql.append("FROM drgs.t_drgs A WHERE A.VER_CODE=(SELECT VER_CODE FROM drgs.t_patientdrg  where  rownum < 2)  \n");
		sql.append("GROUP BY  ('MDC' ||  substr(a.DRG_CODE, 0,1)))y  \n");
		sql.append("	ON x.MDC=y.MDC  where rownum < 6 ORDER BY round(x.CNT/y.CNT,2) ASC  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMdcDrgsByWeightOver1List(
			Map<String, Object> paramMap, String mdcCodeLast5) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  ('MDC' ||  substr(a.DRG_CODE, 0,1)),a.DRG_CODE,a.DRG_DESC,b.AS_WEIGHT FROM drgs.t_drgs a,drgs.t_annualstd b  \n");
		sql.append(" WHERE a.VER_CODE=(SELECT VER_CODE FROM drgs.t_patientdrg  where  rownum < 2)\n");
		sql.append(" AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_YEAR='"
				+ benchmarkYear + "' AND b.AS_WEIGHT >1 \n");
		sql.append(" AND a.DRG_CODE NOT IN( \n");
		sql.append(" SELECT P.PARDRG_CODE FROM drgs.t_patientdrg p ,drgs.t_medicalrecord mr \n");
		sql.append(" WHERE p.PARID=mr.id  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  and  p.mdc IN (" + mdcCode
				+ ") ) AND rownum  < 21  ORDER BY b.AS_WEIGHT DESC 	 \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMdcDrgsByWeightOver9List(
			Map<String, Object> paramMap, String mdcCodeLast5) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ('MDC' ||  substr(a.DRG_CODE, 0,1)),a.DRG_CODE,a.DRG_DESC,b.AS_WEIGHT FROM drgs.t_drgs a,drgs.t_annualstd b  \n");
		sql.append(" WHERE a.VER_CODE=(SELECT VER_CODE FROM drgs.t_patientdrg  where  rownum < 2)\n");
		sql.append(" AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_YEAR='"
				+ benchmarkYear + "' AND b.AS_WEIGHT >9 \n");
		sql.append(" AND a.DRG_CODE NOT IN( \n");
		sql.append(" SELECT P.PARDRG_CODE FROM drgs.t_patientdrg p ,drgs.t_medicalrecord mr \n");
		sql.append(" WHERE p.PARID=mr.id  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  and  p.mdc IN (" + mdcCode
				+ ") ) AND rownum < 21  ORDER BY b.AS_WEIGHT DESC  \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findDrgsFeeList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" 	SELECT * FROM (\n");
		sql.append(" 	SELECT b.PARDRG_CODE,d.DRG_DESC,SUM(a.COSTAMOUNT/c.AS_AVG_FEE)/COUNT(b.PARID) as efffee  \n");
		sql.append(" 	FROM drgs.t_medicalrecord a  \n");
		sql.append(" 	LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" 	LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE \n");
		sql.append("  	LEFT JOIN drgs.t_drgs d ON c.AS_DRG_CODE=d.DRG_CODE \n");
		sql.append(" 	WHERE b.VER_CODE=d.VER_CODE and a.IN_DAYS < 61  \n");
		sql.append("    AND b.PARDRG_CODE is not NULL AND c.AS_YEAR='"
				+ benchmarkYear + "' AND c.AS_WEIGHT is not null \n");
		sql.append("  	AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  	GROUP BY b.PARDRG_CODE,d.DRG_DESC  )m WHERE m.efffee >1 ORDER BY m.efffee DESC\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findDrgsTimeList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" 	SELECT * FROM (\n");
		sql.append(" 	SELECT b.PARDRG_CODE,d.DRG_DESC,SUM(a.IN_DAYS/c.AS_AVG_DAYS)/COUNT(b.PARID) as timefee  \n");
		sql.append(" 	FROM drgs.t_medicalrecord a  \n");
		sql.append(" 	LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" 	LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE \n");
		sql.append("  	LEFT JOIN drgs.t_drgs d ON c.AS_DRG_CODE=d.DRG_CODE \n");
		sql.append(" 	WHERE b.VER_CODE=d.VER_CODE and a.IN_DAYS < 61  \n");
		sql.append("    AND b.PARDRG_CODE is not NULL AND c.AS_YEAR='"
				+ benchmarkYear + "' AND c.AS_WEIGHT is not null \n");
		sql.append("  	AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  	GROUP BY b.PARDRG_CODE,d.DRG_DESC  )m WHERE m.timefee >1 ORDER BY m.timefee DESC\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findMdcDrgsTotalCaseList(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT P.PARDRG_CODE,COUNT(P.PARID) drgsCnt FROM drgs.t_medicalrecord mr  \n");
		sql.append(" LEFT JOIN  drgs.t_patientdrg p ON mr.ID=p.PARID \n");
		sql.append("  WHERE p.MDC in (" + mdcCode
				+ ") AND p.PARDRG_CODE is NOT null AND mr.IN_DAYS < 61 AND  \n");
		sql.append("  to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  GROUP BY P.PARDRG_CODE ORDER BY drgsCnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public String findMdcDRGcnt(Map<String, Object> paramMap) {

		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(d.DRG_CODE) FROM drgs.t_drgs d LEFT JOIN  \n");
		sql.append(" drgs.t_adrg a ON a.ADRG_CODE=d.ADRG_CODE \n");
		sql.append("  WHERE a.VER_CODE=d.VER_CODE AND a.MDC_CODE  in ("
				+ mdcCode + ") \n");
		sql.append("  AND A.VER_CODE=(SELECT DISTINCT (p.ver_code) FROM drgs.t_patientdrg p \n");
		sql.append(" LEFT JOIN drgs.t_medicalrecord mr ON mr.id=p.parid \n");
		sql.append("  WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"') \n");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return String.valueOf(cnt.toString());

	}

	@Override
	public int findDataIsNull(String reportDateFrom, String reportDateTo) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) FROM drgs.t_medicalrecord mr RIGHT JOIN   drgs.t_patientdrg p ON mr.ID=p.PARID  \n");
		sql.append(" WHERE to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		Object cnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return Integer.valueOf(cnt.toString());
	}

	@Override
	public List<Map<String, Object>> operLevelPieInfoList(
			Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.OPER_LEVEL, a.outCaeCnt,b.inCaseCnt,b.cmi,c.CaeCnt-b.inCaseCnt from ( \n");
		sql.append(" SELECT o.OPER_LEVEL,COUNT(mr.ID) outCaeCnt,mr.OUT_DATE outdate FROM drgs.t_medicalrecord mr ,drgs.t_operation o  \n");
		sql.append("  WHERE  mr.ID=o.PARID AND o.OPER_TYPE='M' AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  GROUP BY o.OPER_LEVEL)a LEFT JOIN \n");
		sql.append(" (SELECT o.OPER_LEVEL,count(p.PARID) inCaseCnt, ROUND(SUM(a.AS_WEIGHT)/count(p.PARID),2) cmi  FROM drgs.t_medicalrecord mr , \n");
		sql.append("  drgs.t_patientdrg  p ,drgs.t_operation o,drgs.t_annualstd a \n");
		sql.append("  WHERE  mr.ID=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   and p.PARDRG_CODE is not null  AND mr.IN_DAYS <61 \n");
		sql.append(" AND mr.ID=o.PARID  AND a.AS_YEAR='"
				+ benchmarkYear
				+ "' AND a.as_weight is not null  AND o.OPER_TYPE='M' GROUP BY o.OPER_LEVEL \n");
		sql.append(" ) b ON a.OPER_LEVEL=b.OPER_LEVEL LEFT JOIN \n");
		sql.append(" (SELECT o.OPER_LEVEL,COUNT(mr.ID) CaeCnt FROM drgs.t_medicalrecord mr ,drgs.t_operation o \n");
		sql.append(" WHERE  mr.ID=o.PARID AND o.OPER_TYPE='M' AND mr.IN_DAYS < 61 and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY o.OPER_LEVEL)c ON b.OPER_LEVEL=c.OPER_LEVEL \n");
		sql.append(" ORDER BY a.OPER_LEVEL ASC\n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		StringBuilder sqlHosp = new StringBuilder();
		sqlHosp.append(" SELECT a.outCaeCnt,b.inCaseCnt,b.cmi,c.CaeCnt-b.inCaseCnt from (\n");
		sqlHosp.append(" SELECT COUNT(O.PARID) outCaeCnt FROM drgs.t_medicalrecord mr , drgs.t_operation o \n");
		sqlHosp.append("  WHERE mr.ID=o.PARID AND o.OPER_TYPE='M'  \n");
		sqlHosp.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' )a, \n");
		sqlHosp.append(" (SELECT count(p.PARID) inCaseCnt, ROUND(SUM(a.AS_WEIGHT)/count(p.PARID),2) cmi  FROM drgs.t_medicalrecord mr ,\n");
		sqlHosp.append(" drgs.t_patientdrg  p ,drgs.t_operation o,drgs.t_annualstd a \n");
		sqlHosp.append(" WHERE  mr.ID=p.PARID   AND p.PARDRG_CODE=a.AS_DRG_CODE AND p.PARDRG_CODE is not null   \n");
		sqlHosp.append("  AND mr.ID=o.PARID  AND a.AS_YEAR='"
				+ benchmarkYear
				+ "' AND a.as_weight is not null  AND o.OPER_TYPE='M' AND MR.IN_DAYS <61 \n");
		sqlHosp.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' ) b ,\n");
		sqlHosp.append("( SELECT COUNT(O.PARID) CaeCnt FROM drgs.t_medicalrecord mr , drgs.t_operation o  \n");
		sqlHosp.append("  WHERE mr.ID=o.PARID AND o.OPER_TYPE='M'  AND mr.IN_DAYS <61\n");
		sqlHosp.append("  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"')c\n");
		Object[] hospData = getRepository().createSqlQuery(sqlHosp.toString())
				.singleResult();
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			String obj0 = obj[0] == null ? "0" : obj[0].toString().trim();
			if ("1".equals(obj0)) {
				map.put("operLevel", "一级");
				map.put("caseCnt", obj[1] == null ? 0 : obj[1]);
				map.put("caseCntRatio",
						Arith.div(Double.valueOf(obj[1] + "") * 100,
								Double.valueOf(hospData[0] + ""), 2));
				map.put("inGroupCnt", obj[2] == null ? 0 : obj[2]);
				map.put("CMI", obj[3] == null ? 0 : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? 0 : obj[4]);
				resultList.add(map);
			} else if ("2".equals(obj0)) {
				map.put("operLevel", "二级");
				map.put("caseCnt", obj[1] == null ? 0 : obj[1]);
				map.put("caseCntRatio",
						Arith.div(Double.valueOf(obj[1] + "") * 100,
								Double.valueOf(hospData[0] + ""), 2));
				map.put("inGroupCnt", obj[2] == null ? 0 : obj[2]);
				map.put("CMI", obj[3] == null ? 0 : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? 0 : obj[4]);
				resultList.add(map);
			} else if ("3".equals(obj0)) {
				map.put("operLevel", "三级");
				map.put("caseCnt", obj[1] == null ? 0 : obj[1]);
				map.put("caseCntRatio",
						Arith.div(Double.valueOf(obj[1] + "") * 100,
								Double.valueOf(hospData[0] + ""), 2));
				map.put("inGroupCnt", obj[2] == null ? 0 : obj[2]);
				map.put("CMI", obj[3] == null ? 0 : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? 0 : obj[4]);
				resultList.add(map);
			} else if ("4".equals(obj0)) {
				map.put("operLevel", "四级");
				map.put("caseCnt", obj[1] == null ? 0 : obj[1]);
				map.put("caseCntRatio",
						Arith.div(Double.valueOf(obj[1] + "") * 100,
								Double.valueOf(hospData[0] + ""), 2));
				map.put("inGroupCnt", obj[2] == null ? 0 : obj[2]);
				map.put("CMI", obj[3] == null ? 0 : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? 0 : obj[4]);
				resultList.add(map);
			} else {
				map.put("operLevel", "无级");
				map.put("caseCnt", obj[1] == null ? 0 : obj[1]);
				map.put("caseCntRatio",
						Arith.div(Double.valueOf(obj[1] + "") * 100,
								Double.valueOf(hospData[0] + ""), 2));
				map.put("inGroupCnt", obj[2] == null ? 0 : obj[2]);
				map.put("CMI", obj[3] == null ? 0 : obj[3]);
				map.put("notInGroupCnt", obj[4] == null ? 0 : obj[4]);
				resultList.add(map);
			}

		}
		return resultList;
	}

	@Override
	public List<Object[]> findNewDrgs(String reportDateFrom, String reportDateTo,
			String benchmarkYear) {
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  p.PARDRG_CODE, COUNT(p.PARDRG_CODE ) FROM drgs.t_medicalrecord mr \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p ON mr.ID=p.PARID \n");
		sql.append(" WHERE mr.IN_DAYS <61 AND p.PARDRG_CODE is NOT NULL \n");
		sql.append(" AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND p.PARDRG_CODE NOT in (SELECT AS_DRG_CODE FROM drgs.t_annualstd WHERE AS_YEAR=:benchmarkYear AND as_weight is not null)  GROUP BY p.PARDRG_CODE   \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list;
	}

	@Override
	public Object[] findSimpHospBaseTable4feetime(Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Object[]> findOperCaseByLvel(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT OP.OPER_LEVEL,COUNT(OP.PARID) \n");
		sql.append(" FROM drgs.t_medicalrecord mr ,drgs.t_operation OP \n");
		sql.append(" WHERE mr.id=OP.PARID and OP.OPER_TYPE='M' \n");
		sql.append(" and to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY OP.OPER_LEVEL ORDER BY COUNT(OP.PARID) DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Object[]> findTop5OperCode(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT B.OPER_CODE,COUNT(b.PARID) as cnt FROM drgs.t_medicalrecord a LEFT JOIN drgs.t_operation b on a.id=b.PARID  \n");
		sql.append(" WHERE b.OPER_TYPE='M' and rownum < 6 \n");
		sql.append(" AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY b.OPER_CODE ORDER BY cnt DESC   \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;
	}

	@Override
	public List<Map<String, Object>> top5OpercodedepListByOperCode(
			Map<String, Object> paramMap, Object operCode) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT b.OPER_CODE,b.OPERATION,b.OPER_LEVEL,a.OUT_DEP,COUNT(a.ID) as cnt \n");
		sql.append(" from drgs.t_medicalrecord a \n");
		sql.append(" LEFT JOIN drgs.t_operation  b ON a.id=b.PARID \n");
		sql.append(" WHERE b.OPER_TYPE='M' AND b.OPER_CODE='" + operCode
				+ "' \n");
		sql.append(" AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" GROUP BY  b.OPER_CODE,b.OPERATION,b.OPER_LEVEL,a.OUT_DEP  ORDER BY cnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		sql = new StringBuilder();
		sql.append(" SELECT count(b.parid) as cnt \n");
		sql.append(" from drgs.t_medicalrecord a \n");
		sql.append(" LEFT JOIN drgs.t_operation  b ON a.id=b.PARID \n");
		sql.append(" WHERE b.OPER_TYPE='M' AND b.OPER_CODE='" + operCode
				+ "' \n");
		sql.append(" AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		Object totalcnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		int othDepopercnt = 0;
		int depcnt = 0;
		if (!list.isEmpty()) {
			for (int i = 0; i < list.size(); i++) {
				Object[] obj = list.get(i);
				Object[] objo = list.get(i);
				if (i > 0) {
					objo = list.get(i - 1);
				}
				Map<String, Object> map = new HashMap<String, Object>();
				if (obj[0] != objo[0]) {
					depcnt++;// 解决同一个手术不同级别问题
				}
				if (depcnt < 3) {
					map.put("order", i);
					map.put("opercode", obj[0] == null ? "--" : obj[0]);
					map.put("operation", obj[1] == null ? "--" : obj[1]);
					map.put("operlevel", obj[2] == null ? "--" : obj[2]);
					map.put("depname", obj[3] == null ? "--" : obj[3]);
					map.put("opercnt", obj[4] == null ? 0 : obj[4]);
					if (Double.valueOf(totalcnt == null ? "0" : totalcnt + "")
							.intValue() != 0) {
						map.put("operdepratio", Arith.mul(Arith.div(Double
								.valueOf(obj[4] == null ? "0" : obj[4] + ""),
								Double.valueOf(totalcnt == null ? "0"
										: totalcnt + ""), 4), 100));
					} else {
						map.put("operdepratio", 0);
					}
					mapList.add(map);
				} else {
					othDepopercnt += Double.valueOf(obj[4] == null ? "0"
							: obj[4] + "");
				}
			}
		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("order", 1);
		map.put("opercode", "--");
		map.put("operation", "--");
		map.put("operlevel", "--");
		map.put("depname", "其他");
		map.put("opercnt", othDepopercnt);
		map.put("operdepratio",
				Arith.mul(Arith.div(othDepopercnt,
						Double.valueOf(totalcnt == null ? "0" : totalcnt + ""),
						4), 100));
		mapList.add(map);
		return mapList;
	}

	@Override
	public List<Map<String, Object>> findmdc() {
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		String sql = "SELECT a.mdcdesc,a.fee,a.time FROM drgs.t_mdctest a ";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("mdc", obj[0]);
			map.put("fee", obj[1]);
			map.put("time", obj[2]);
			mapList.add(map);
		}

		return mapList;
	}

	@Override
	public int findMdcCnt(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT COUNT(DISTINCT b.MDC) from  \n");
		sql.append("  drgs.t_medicalrecord a LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append("  where  to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		Object mdcCnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		int mdcCntint = Integer.valueOf(mdcCnt == null ? "0" : mdcCnt + "");
		return mdcCntint;
	}

	@Override
	public List<Object[]> mdcFTRisk1List(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
	
		map.put("benchmarkYear", benchmarkYear);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.mdc,a.MDC_DESC,a.incnt,a.deadcnt,(a.deadcnt/a.incnt)*100 FROM (  \n");
		sql.append(" SELECT b.mdc,c.MDC_DESC,sum(case when d.AS_RISK_LEVEL='1' then 1 else 0 end) as incnt, \n");
		sql.append(" sum(case when d.AS_RISK_LEVEL='1' AND a.OUT_WAY='5' then 1 else 0 end) as deadcnt \n");
		sql.append(" FROM drgs.t_medicalrecord a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" LEFT JOIN drgs.t_mdcgroup c ON b.MDC=c.MDC_CODE \n");
		sql.append(" LEFT JOIN drgs.t_annualstd d on b.PARDRG_CODE=d.AS_DRG_CODE \n");
		sql.append(" WHERE b.VER_CODE=c.VER_CODE AND b.PARDRG_CODE is not NULL \n");
		sql.append(" and to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append(" AND d.AS_YEAR=:benchmarkYear   GROUP BY b.mdc,c.MDC_DESC \n");
		sql.append(" )a WHERE a.incnt<>0  ORDER BY a.deadcnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list;
	}

	@Override
	public List<Object[]> mdcFTRisk2List(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT a.mdc,a.MDC_DESC,a.incnt,a.deadcnt,(a.deadcnt/a.incnt)*100 FROM (  \n");
		sql.append(" SELECT b.mdc,c.MDC_DESC,sum(case when d.AS_RISK_LEVEL='2' then 1 else 0 end) as incnt, \n");
		sql.append(" sum(case when d.AS_RISK_LEVEL='2' AND a.OUT_WAY='5' then 1 else 0 end) as deadcnt \n");
		sql.append(" FROM drgs.t_medicalrecord a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" LEFT JOIN drgs.t_mdcgroup c ON b.MDC=c.MDC_CODE \n");
		sql.append(" LEFT JOIN drgs.t_annualstd d on b.PARDRG_CODE=d.AS_DRG_CODE \n");
		sql.append(" WHERE b.VER_CODE=c.VER_CODE AND b.PARDRG_CODE is not NULL \n");
		sql.append(" and to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n");
		sql.append(" AND d.AS_YEAR=:benchmarkYear   GROUP BY b.mdc,c.MDC_DESC\n");
		sql.append(" )a WHERE a.incnt<>0  ORDER BY a.deadcnt DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list;
	}

	@Override
	public List<Object[]> findMdcDesc(Map<String, Object> paramMap) {
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  DISTINCT  a.MDC_CODE,a.MDC_DESC FROM drgs.t_mdcgroup a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.VER_CODE=b.VER_CODE  \n");
		sql.append(" WHERE  a.MDC_CODE in (" + mdcCode + ") \n");
		List<Object[]> mdclist = getRepository().createSqlQuery(sql.toString())
				.list();
		return mdclist;
	}

	public int findMdcCaseCnt(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(b.PARID) FROM drgs.t_medicalrecord a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" WHERE a.IN_DAYS<61 AND b.PARDRG_CODE is not NULL \n");
		sql.append("  and to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" and b.MDC in (" + mdcCode + ") \n");
		Object casecnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return Integer.parseInt(casecnt == null ? "0" : casecnt + "");
	}

	@Override
	public int findMdcDRGsCnt(Map<String, Object> paramMap) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT(b.PARDRG_CODE)) FROM drgs.t_medicalrecord a  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg b on a.id=b.PARID \n");
		sql.append(" WHERE a.IN_DAYS<61 AND b.PARDRG_CODE is not NULL \n");
		sql.append("  and to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" and b.MDC in (" + mdcCode + ") \n");
		Object casecnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return Integer.parseInt(casecnt == null ? "0" : casecnt + "");
	}

	@Override
	public int findMdcDRGsRealCnt(Map<String, Object> paramMap) {
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(DISTINCT A.DRG_CODE) FROM drgs.t_drgs a WHERE  ('MDC' ||  substr(a.DRG_CODE, 0,1))  in ("
				+ mdcCode
				+ ")  AND a.VER_CODE  in (SELECT DISTINCT b.VER_CODE FROM drgs.t_patientdrg b )  \n");
		Object drgscnt = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return Integer.parseInt(drgscnt == null ? "0" : drgscnt + "");
	}

	@Override
	public List<Map<String, Object>> findmdcDdrgtop10MapList(
			Map<String, Object> paramMap) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		int casecnt = Integer.valueOf(paramMap == null ? "0" : paramMap
				.get("mdcCasecnt") + "");
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT B.PARDRG_CODE,D.DRG_DESC,count(B.PARID) AS CNT,C.AS_WEIGHT FROM drgs.t_medicalrecord A  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.ID=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE C.AS_YEAR='" + benchmarkYear
				+ "' AND A.IN_DAYS <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append("AND to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in (" + mdcCode
				+ ") and  rownum < 21  \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY CNT DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		int i = 1;
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("order", i);
			map.put("drgcode", obj[0] + "");
			map.put("drgdesc", obj[1] + "");
			map.put("outcase", obj[2] + "");
			if (casecnt != 0) {
				map.put("caseratio",
						Arith.mul(
								Arith.div(Double.valueOf(obj[2] == null ? "0"
										: obj[2] + ""), Double.valueOf(casecnt
										+ ""), 4), 100));
			} else {
				map.put("caseratio", 0);
			}
			map.put("weight", obj[3] + "");
			i++;
			mapList.add(map);
		}
		return mapList;
	}

	@Override
	public List<Map<String, Object>> findmdcdrgweighttop10List(
			Map<String, Object> paramMap) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT sum(C.AS_WEIGHT) FROM drgs.t_medicalrecord A  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.ID=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE C.AS_YEAR='" + benchmarkYear
				+ "' AND A.IN_DAYS <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append("AND to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in (" + mdcCode
				+ ") \n");
		Object mdctotalweight = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		double mdctotalweightd = Double.valueOf(mdctotalweight == null ? "0"
				: mdctotalweight + "");
		sql = new StringBuilder();
		sql.append(" SELECT B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT weight ,count(B.PARID) AS CNT,count(B.PARID)*C.AS_WEIGHT totalweight FROM drgs.t_medicalrecord A  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg B ON A.ID=B.PARID \n");
		sql.append(" LEFT JOIN drgs.t_annualstd C ON B.PARDRG_CODE=C.AS_DRG_CODE \n");
		sql.append(" LEFT JOIN drgs.t_drgs D ON B.PARDRG_CODE=D.DRG_CODE  \n");
		sql.append(" WHERE C.AS_YEAR='" + benchmarkYear
				+ "' AND A.IN_DAYS <61 AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append("AND to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'  \n");
		sql.append(" AND  B.VER_CODE=D.VER_CODE AND B.MDC in (" + mdcCode
				+ ")   and  rownum < 21 \n");
		sql.append(" GROUP BY B.PARDRG_CODE,D.DRG_DESC,C.AS_WEIGHT  ORDER BY totalweight DESC \n");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		int i = 1;
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("order", i);
			map.put("drgcode", obj[0] + "");
			map.put("drgdesc", obj[1] + "");
			map.put("weight", obj[2] + "");
			map.put("outcase", obj[3] + "");
			map.put("totalweight", obj[4] + "");
			map.put("mdctotalweight", mdctotalweight);

			if (mdctotalweightd != 0) {
				map.put("weightratio",
						Arith.mul(Arith.div(
								Double.valueOf(obj[4] == null ? "0" : obj[4]
										+ ""), mdctotalweightd, 4), 100));
			} else {
				map.put("weightratio", 0);
			}
			i++;
			mapList.add(map);
		}
		return mapList;
	}

	@Override
	public List<Object[]> findmainOperByLevelInfo(Map<String, Object> paramMap,
			String level) {
		String dateFrom = paramMap == null ? "" : paramMap
				.get("reportDateFrom").toString();
		String dateTo = paramMap == null ? "" : paramMap.get("reportDateTo")
				.toString();
		StringBuilder sql = new StringBuilder();
		if ("4".equals(level) || "3".equals(level) || "2".equals(level)
				|| "1".equals(level)) {
			sql.append("  SELECT  B.OPER_CODE, b.OPERATION, COUNT(B.PARID) CNT FROM drgs.t_medicalrecord a LEFT JOIN drgs.t_operation b on a.id=b.PARID  \n");
			sql.append("  WHERE b.OPER_TYPE='M' AND B.OPER_LEVEL='" + level
					+ "' AND \n");
			sql.append("    to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'   \n");
			sql.append("  GROUP BY B.OPER_CODE, b.OPERATION ORDER BY CNT DESC \n");
		} else {
			sql.append("  SELECT  B.OPER_CODE, b.OPERATION, COUNT(B.PARID) CNT FROM drgs.t_medicalrecord a LEFT JOIN drgs.t_operation b on a.id=b.PARID  \n");
			sql.append("  WHERE b.OPER_TYPE='M' AND B.OPER_LEVEL not in ('1','2','3','4') AND \n");
			sql.append("    to_char(a.out_date,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"'   \n");
			sql.append("  GROUP BY B.OPER_CODE, b.OPERATION ORDER BY CNT DESC \n");
		}

		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.list();
		return list;

	}

	@Override
	public int findMdcDrgsByWeightOver1Cnt(Map<String, Object> paramMap,
			String mdcCodeLast5) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String mdcCode = paramMap == null ? "" : paramMap.get("mdcCode")
				.toString();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(1) FROM drgs.t_drgs a,drgs.t_annualstd b  \n");
		sql.append(" WHERE a.VER_CODE=(SELECT VER_CODE FROM drgs.t_patientdrg  where  rownum < 2)\n");
		sql.append(" AND a.DRG_CODE=b.AS_DRG_CODE AND b.AS_YEAR='"
				+ benchmarkYear + "' AND b.AS_WEIGHT >1 \n");
		sql.append(" AND a.DRG_CODE NOT IN( \n");
		sql.append(" SELECT P.PARDRG_CODE FROM drgs.t_patientdrg p ,drgs.t_medicalrecord mr \n");
		sql.append(" WHERE p.PARID=mr.id  AND to_char(mr.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("  and  p.mdc IN (" + mdcCode + ") ) \n");
		Object cntObj = getRepository().createSqlQuery(sql.toString())
				.singleResult();
		return cntObj == null ? 0 : Double.valueOf(cntObj.toString())
				.intValue();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findhospTable3(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findhospTable3(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		
		StringBuffer sql = new StringBuffer();
		sql.append("	  SELECT ROUND(SUM(CASE WHEN G.ORDER_FIRSTPAGE_FEE=232 THEN 1 ELSE 0 END)/COUNT(1)*100,2) A, \n");
		sql.append("       ROUND(SUM(CASE WHEN G.ORDER_FIRSTPAGE_FEE=232 THEN G.ORDER_TOTAL_FEE ELSE NULL END)/SUM(G.ORDER_TOTAL_FEE)*100,2) B, \n");
		sql.append("      ROUND(SUM(CASE WHEN G.ORDER_FIRSTPAGE_FEE=232 THEN G.ORDER_TOTAL_FEE ELSE NULL END)/SUM(CASE WHEN G.ORDER_FIRSTPAGE_FEE=232 \n");
		sql.append("     OR G.ORDER_FIRSTPAGE_FEE=231 OR G.ORDER_FIRSTPAGE_FEE=240 OR G.ORDER_FIRSTPAGE_FEE=250  THEN G.ORDER_TOTAL_FEE ELSE NULL END)*100,2) C \n");
		sql.append("     FROM DRGS.T_MEDICALRECORD H \n");
		sql.append("     LEFT JOIN DRGS.T_ORDER_RANGE_MONTH G  ON G.ORDER_MR_ID=H.RECORD_ID \n");
		sql.append("     WHERE  TO_CHAR(H.OUT_DATE,'YYYY-MM-DD') BETWEEN '"+reportDateFrom+"' AND '"+reportDateTo+"' \n");
		List<Object[]> hospTable3 = getRepository().createSqlQuery(sql.toString())
				.list();
		return hospTable3;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findhospTable4(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findhospTable4(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT ROUND(SUM(CASE WHEN INFECTION_CASE = 1 THEN 1 ELSE 0 END)/COUNT(1)*100,2), \n");
		sql.append("     ROUND(SUM(CASE WHEN CPW_CASE = 1 THEN 1 ELSE 0 END)/COUNT(1)*100,2) \n");
		sql.append("        FROM   DRGS.T_MEDICALRECORD \n");
		sql.append("     WHERE   to_char(out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		List<Object[]> hospTable4 = getRepository().createSqlQuery(sql.toString())
				  .list();
		return hospTable4;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findHospdep1(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findHospdep1(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();

		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);

		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT  SUM(CASE WHEN F.DEP_NAME =:dephospname THEN 1 ELSE 0 END),ROUND(SUM(CASE WHEN F.DEP_NAME=:dephospname THEN 1 ELSE 0 END)/COUNT(1)*100,2) FROM DRGs.T_MEDICALRECORD A \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD E   ON E.DR_EPISODE_ID=A.RECORD_ID \n");
		sql.append("      LEFT JOIN DRGs.T_DEPARTMENT F  ON E.DR_OUT_DEP =F.DEP_NAME   \n");
		sql.append("     WHERE to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		List<Object[]> Hospdep1 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return Hospdep1;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findHospdep2(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findHospdep2(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT  sum(count(b.mdc)),count(distinct b.PARDRG_CODE ),count(distinct B.MDC ) FROM DRGs.T_MEDICALRECORD A   \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("        WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("          AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE  \n");
		sql.append("          AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("          and f.dep_name=:dephospname \n");
		sql.append("         AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  b.PARDRG_CODE,B.MDC,D.MDC_DESC    \n");
		List<Object[]> Hospdep2 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return Hospdep2;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findHospdep3(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findHospdep3(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("  select h.mdc,h.MDC_DESC,h.cntRatio from(    \n");
		sql.append("  SELECT B.MDC,D.MDC_DESC,count(b.mdc) bingliCnt,round(ratio_to_report(count(b.mdc)) over(),4)*100 cntRatio,round(SUM(C.AS_WEIGHT)/COUNT(B.PARID),2) binglicmi FROM DRGs.T_MEDICALRECORD A    \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID  \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE   \n");
		sql.append("      LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE   \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id  \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name  \n");
		sql.append("         WHERE C.AS_WEIGHT IS NOT NULL    \n");
		sql.append("            AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE   \n");
		sql.append("            AND C.AS_YEAR=:benchmarkYear  \n");
		sql.append("            and f.dep_name=:dephospname  \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.MDC,D.MDC_DESC   ORDER BY bingliCnt DESC) h  \n");
		sql.append("       where rownum=1  \n");
		List<Object[]> Hospdep3 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return Hospdep3;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findHospdep4(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findHospdep4(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String fristhospdepmdc = paramMap == null ? "" : paramMap.get(
				"fristhospdepmdc").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		map.put("fristhospdepmdc", fristhospdepmdc);
		StringBuffer sql = new StringBuffer();
		sql.append("      select sum(bingliCnt) zongbingli,ROUND(SUM(CASE WHEN z.dep_name=:dephospname THEN z.binglicnt ELSE 0 END)/sum(bingliCnt)*100,2) keshiratio \n");
		sql.append("      from(    SELECT f.dep_name,count(b.mdc) bingliCnt FROM DRGs.T_MEDICALRECORD  A   \n");
		sql.append("                LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("                LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("                LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("                left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("                left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("                    WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("                      AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE  \n");
		sql.append("                      AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("                      and b.mdc=:fristhospdepmdc \n");
		sql.append("                      AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("                GROUP BY  f.dep_name,B.MDC,D.MDC_DESC   ORDER BY bingliCnt DESC) z \n");
		List<Object[]> Hospdep4 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return Hospdep4;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findtable61(java.
	 * util.Map)
	 */
	@Override
	public List<Object[]> findtable61(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT B.MDC,D.MDC_DESC,count(b.mdc) bingliCnt,round(ratio_to_report(count(b.mdc)) over(),4) *100 cntRatio,round(SUM(C.AS_WEIGHT)/COUNT(B.PARID),2) binglicmi FROM DRGs.T_MEDICALRECORD A \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("         WHERE C.AS_WEIGHT IS NOT NULL  \n");
		sql.append("            AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE \n");
		sql.append("            AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("            and f.dep_name=:dephospname \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.MDC,D.MDC_DESC   ORDER BY bingliCnt DESC \n");
		List<Object[]> list61 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list61;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findtable62(java.
	 * util.Map)
	 */
	@Override
	public List<Object[]> findtable62(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT B.MDC,D.MDC_DESC,count(b.mdc) bingliCnt,SUM(C.AS_WEIGHT) MDCTOTALWEIGHT ,round(ratio_to_report(SUM(C.AS_WEIGHT)) over(),4) *100 weiRatio FROM DRGs.T_MEDICALRECORD A  \n");
		sql.append("    LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("    LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("    LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("    left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("    left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("        WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("          AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE  \n");
		sql.append("          AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("          and f.dep_name=:dephospname \n");
		sql.append("         AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("     GROUP BY  B.MDC,D.MDC_DESC   ORDER BY MDCTOTALWEIGHT DESC \n");
		List<Object[]> list62 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list62;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findHospdep5(java
	 * .util.Map)
	 */
	@Override
	public List<Object[]> findHospdep5(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      SELECT Z.MDC,Z.MDC_DESC,Z.weiRatio   FROM( \n");
		sql.append("        SELECT B.MDC,D.MDC_DESC,count(b.mdc) bingliCnt,SUM(C.AS_WEIGHT) MDCTOTALWEIGHT ,round(ratio_to_report(SUM(C.AS_WEIGHT)) over(),4) *100 weiRatio FROM DRGs.T_MEDICALRECORD A  \n");
		sql.append("        LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("        LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("        LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("        left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("        left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("            WHERE C.AS_WEIGHT IS NOT NULL \n");
		sql.append("              AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE  \n");
		sql.append("         AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("          and f.dep_name=:dephospname \n");
		sql.append("          AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("         GROUP BY  B.MDC,D.MDC_DESC   ORDER BY MDCTOTALWEIGHT DESC) Z \n");
		sql.append("       WHERE ROWNUM =1 \n");
		List<Object[]> Hospdep5 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return Hospdep5;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findtable63(java.
	 * util.Map)
	 */
	@Override
	public List<Object[]> findtable63(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>(); 
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      SELECT G.MDC,H.MDC_DESC, \n");
		sql.append("          ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2),  \n");
		sql.append("          ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)   \n");
		sql.append("          FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("                (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE,T.AS_MDC_CODE,A.MDC \n");
		sql.append("                 FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("                 WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("                 AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  \n");
		sql.append("                 AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("                 AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 ))  G    ON E.ID = G.PARID \n");
		sql.append("        LEFT JOIN DRGs.T_MDCGROUP H ON G.AS_MDC_CODE=H.MDC_CODE  \n");
		sql.append("        LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("        WHERE  E.OUT_DEP=:dephospname \n");
		sql.append("        GROUP BY G.MDC,H.MDC_DESC  \n");
		sql.append("        ORDER BY ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC, ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  DESC \n");
		List<Object[]> list63 = getRepository().createSqlQuery(sql.toString())
				.setParameters(map).list();
		return list63;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findtable63timetop5
	 * (java.util.Map)
	 */
	@Override
	public List<Object[]> findtable63timetop5(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      SELECT G.MDC,H.MDC_DESC, \n");
		sql.append("          ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)   \n");
		sql.append("          FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("                (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE,T.AS_MDC_CODE,A.MDC \n");
		sql.append("                 FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("                 WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("                 AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  \n");
		sql.append("                 AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("                 AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                ))  G    ON E.ID = G.PARID \n");
		sql.append("        LEFT JOIN DRGs.T_MDCGROUP H ON G.AS_MDC_CODE=H.MDC_CODE  \n");
		sql.append("        LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("        WHERE  E.OUT_DEP=:dephospname \n");
		sql.append("        GROUP BY G.MDC,H.MDC_DESC  \n");
		sql.append("        ORDER BY  ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  DESC \n");
		List<Object[]> list63timetop5 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return list63timetop5;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.dhcc.DRGsHosp.application.WordReportApplication#findtable63feetop5
	 * (java.util.Map)
	 */
	@Override
	public List<Object[]> findtable63feetop5(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      SELECT G.MDC,H.MDC_DESC, \n");
		sql.append("          ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID)*100,2)  \n");
		sql.append("          FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("                (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE,T.AS_MDC_CODE,A.MDC \n");
		sql.append("                 FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("                 WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("                 AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL  \n");
		sql.append("                 AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("                 AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 ))  G    ON E.ID = G.PARID \n");
		sql.append("        LEFT JOIN DRGs.T_MDCGROUP H ON G.AS_MDC_CODE=H.MDC_CODE  \n");
		sql.append("        LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("        WHERE  E.OUT_DEP=:dephospname \n");
		sql.append("        GROUP BY G.MDC,H.MDC_DESC  \n");
		sql.append("        ORDER BY ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC \n");
		List<Object[]> list63feetop5 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return list63feetop5;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findhospdep64(java.util.Map)
	 */
	@Override
	public List<Object[]> findhospdep64(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT  count(*) ,SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END),ROUND(SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END)/COUNT(1)*100,2) FROM DRGs.T_MEDICALRECORD A   \n");
		sql.append("     LEFT JOIN DRGs.T_DAILYRECORD E   ON E.DR_EPISODE_ID=A.RECORD_ID \n");
		sql.append("     LEFT JOIN DRGs.T_DEPARTMENT F  ON E.DR_OUT_DEP =F.DEP_NAME     \n");
		sql.append("     WHERE F.DEP_NAME=:dephospname \n");
		sql.append("     and to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		List<Object[]> findhospdep64 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findhospdep64;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable64(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable64(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT Z.MDC,Z.MDC_DESC,Z.bingliCnt,Z.siwang,Z.SIWANGLV \n");
		sql.append("    FROM( \n");
		sql.append("    SELECT B.MDC,D.MDC_DESC,count(b.mdc) bingliCnt,sum(case when OUT_WAY=5 then 1 else 0 end) siwang,round(sum(case when OUT_WAY=5 then 1 else 0 end)/count(b.mdc),2) SIWANGLV FROM DRGs.T_MEDICALRECORD A  \n"); 
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_MDCGROUP D ON C.AS_MDC_CODE=D.MDC_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("          WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("            AND A.IN_DAYS < 61 AND B.VER_CODE=D.VER_CODE \n"); 
		sql.append("           AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("            and f.dep_name=:dephospname \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.MDC,D.MDC_DESC   ORDER BY bingliCnt DESC) Z \n");
		sql.append("       WHERE Z.SIWANG>0 \n");
		List<Object[]> findtable64 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable64;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable65(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable65(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT B.PARDRG_CODE,H.CSD_DRG_DESC,count(B.PARDRG_CODE) bingliCnt,round(ratio_to_report(count(B.PARDRG_CODE)) over(),4) *100,C.AS_WEIGHT    FROM DRGs.T_MEDICALRECORD A   \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=B.PARDRG_CODE \n");
		sql.append("          WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("            AND A.IN_DAYS < 61 \n");
		sql.append("            AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("            and f.dep_name=:dephospname \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.PARDRG_CODE,H.CSD_DRG_DESC ,C.AS_WEIGHT  ORDER BY bingliCnt DESC \n");
		List<Object[]> findtable65 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable65;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable66(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable66(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT B.PARDRG_CODE,H.CSD_DRG_DESC,C.AS_WEIGHT,count(B.PARDRG_CODE) bingliCnt,sum(C.AS_WEIGHT) zongweight,round(ratio_to_report(sum(C.AS_WEIGHT)) over(),4) *100   FROM DRGs.T_MEDICALRECORD A   \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=B.PARDRG_CODE \n");
		sql.append("          WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("            AND A.IN_DAYS < 61 \n");
		sql.append("            AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("            and f.dep_name=:dephospname \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.PARDRG_CODE,H.CSD_DRG_DESC ,C.AS_WEIGHT  ORDER BY zongweight DESC \n");
		List<Object[]> findtable66 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable66;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findchart15(java.util.Map)
	 */
	@Override
	public List<Object[]> findchart15(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT G.AS_DRG_CODE,H.CSD_DRG_DESC, \n");
		sql.append("        ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2),  \n");
		sql.append("        ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)   \n");
		sql.append("        FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("              (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE \n");
		sql.append("               FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("               WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("               AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL \n");  
		sql.append("               AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("               AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("               ))  G    ON E.ID = G.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=G.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("      WHERE  E.OUT_DEP=:dephospname\n");
		sql.append("      GROUP BY G.AS_DRG_CODE ,H.CSD_DRG_DESC \n");
		sql.append("      ORDER BY ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC, ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  DESC \n");
		List<Object[]> findchart15 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findchart15;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findcharttimetop5(java.util.Map)
	 */
	@Override
	public List<Object[]> findcharttimetop5(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT G.AS_DRG_CODE,H.CSD_DRG_DESC, \n");
		sql.append("        ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)   \n");
		sql.append("        FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("              (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE \n");
		sql.append("               FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("               WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("               AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL \n");  
		sql.append("               AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("               AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("               ))  G    ON E.ID = G.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=G.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("      WHERE  E.OUT_DEP=:dephospname\n");
		sql.append("      GROUP BY G.AS_DRG_CODE ,H.CSD_DRG_DESC \n");
		sql.append("      ORDER BY ROUND(SUM(E.IN_DAYS/G.AS_AVG_DAYS)/COUNT(G.PARID),2)  DESC \n");
		List<Object[]> findcharttimetop5 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findcharttimetop5;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findchartfeetop5(java.util.Map)
	 */
	@Override
	public List<Object[]> findchartfeetop5(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT G.AS_DRG_CODE,H.CSD_DRG_DESC, \n");
		sql.append("        ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2)  \n");
		sql.append("        FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("              (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE \n");
		sql.append("               FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("               WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("               AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL \n");  
		sql.append("               AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("               AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("               ))  G    ON E.ID = G.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=G.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("      WHERE  E.OUT_DEP=:dephospname\n");
		sql.append("      GROUP BY G.AS_DRG_CODE ,H.CSD_DRG_DESC \n");
		sql.append("      ORDER BY ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC  \n");
		List<Object[]> findchartfeetop5 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findchartfeetop5;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable67(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable67(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		String AbnormalStr = paramMap == null ? null :
			   ( "".equals(paramMap.get("AbnormalStr").toString())|| paramMap.get("AbnormalStr").toString()== null ? null : paramMap.get("AbnormalStr").toString() );
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT G.AS_DRG_CODE,H.CSD_DRG_DESC,g.as_weight,count(G.AS_DRG_CODE),g.AS_AVG_FEE,g.AS_AVG_DAYS \n");
		sql.append("           FROM  DRGs.T_MEDICALRECORD E RIGHT JOIN  \n");
		sql.append("              (SELECT A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_DRG_CODE,t.as_weight \n");
		sql.append("               FROM DRGs.T_PATIENTDRG A, DRGs.T_ANNUALSTD T  \n");
		sql.append("              WHERE A.PARDRG_CODE = T.AS_DRG_CODE AND T.AS_YEAR =:benchmarkYear  \n");
		sql.append("               AND EXISTS (SELECT B.ID FROM DRGs.T_MEDICALRECORD B WHERE A.PARID = B.ID AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL \n");  
		sql.append("               AND B.IN_DAYS <61 AND A.PARDRG_CODE IS NOT NULL   \n");
		sql.append("               AND to_char(b.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("               ))  G    ON E.ID = G.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=G.AS_DRG_CODE  \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=E.RECORD_ID \n");
		sql.append("      WHERE  E.OUT_DEP=:dephospname \n");
		sql.append("        and  G.PARDRG_CODE in (" + AbnormalStr + ") \n");
		sql.append("      GROUP BY G.AS_DRG_CODE ,H.CSD_DRG_DESC,g.as_weight,g.AS_AVG_FEE,g.AS_AVG_DAYS \n");
		sql.append("      ORDER BY ROUND(SUM(E.COSTAMOUNT/G.AS_AVG_FEE)/COUNT(G.PARID),2) DESC \n");
		List<Object[]> findtable67 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable67;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable68(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable68(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String dephospname = paramMap == null ? "" : paramMap
				.get("dephospname").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("dephospname", dephospname);
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    SELECT  Z.PARDRG_CODE,z.csd_drg_desc,z.AS_RISK_LEVEL,Z.bingliCnt,Z.siwang,Z.SIWANGLV \n");
		sql.append("    FROM( \n");
		sql.append("    SELECT B.PARDRG_CODE,h.csd_drg_desc,count(b.mdc) bingliCnt,  \n");
		sql.append("    case when c.AS_RISK_LEVEL=0 THEN '无' when c.AS_RISK_LEVEL=1 then '低' when c.AS_RISK_LEVEL=2 then '中低' when c.AS_RISK_LEVEL=3 then '中高' when c.AS_RISK_LEVEL=4 then '高' end AS_RISK_LEVEL ,sum(case when OUT_WAY=5 then 1 else 0 end) siwang,round(sum(case when OUT_WAY=5 then 1 else 0 end)/count(b.mdc),4)*100 SIWANGLV  \n");
		sql.append("    FROM DRGs.T_MEDICALRECORD A   \n");
		sql.append("      LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID \n");
		sql.append("      LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE  \n");
		sql.append("      left join DRGs.T_DAILYRECORD e   on e.dr_episode_id=a.record_id \n");
		sql.append("      left join DRGs.T_DEPARTMENT f  on e.DR_OUT_DEP =f.dep_name \n");
		sql.append("     LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=c.as_drg_code \n");
		sql.append("          WHERE C.AS_WEIGHT IS NOT NULL   \n");
		sql.append("            AND C.AS_YEAR=:benchmarkYear \n");
		sql.append("            and f.dep_name=:dephospname \n");
		sql.append("           and H.CSD_DRG_CODE=b.pardrg_code \n");
		sql.append("            AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		sql.append("       GROUP BY  B.PARDRG_CODE,AS_RISK_LEVEL,h.csd_drg_desc   ORDER BY bingliCnt DESC) Z \n");
		sql.append("      WHERE Z.SIWANG>0 \n");
		   
		List<Object[]> findtable68 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable68;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#finddiagnose7_1(java.util.Map)
	 */
	@Override
	public List<Object[]> finddiagnose7_1(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    select sum(case when a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end) tr1, \n");
		sql.append("           round(sum(case when a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end)/count(1)*100,2) tr2, \n");
		sql.append("           sum(case when  b.pardrg_code IS  not NULL AND a.IN_DAYS <61 and a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end) tr3 , \n");
		sql.append("           sum(case when a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end)- sum(case when  b.pardrg_code IS  not NULL AND a.IN_DAYS <61 and a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end)  , \n");
		sql.append("           sum(case when a.diagnose_code_main in (" + dephospcodeStr + ") and a.IN_DAYS >60  then 1 else 0 end), \n");
		sql.append("           round(sum(case when  b.pardrg_code IS  not NULL AND a.IN_DAYS <61 and a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end)*100/sum(case when a.diagnose_code_main in (" + dephospcodeStr + ") then 1 else 0 end),2)  \n");
		sql.append("     from DRGs.T_MEDICALRECORD a  \n");
		sql.append("    LEFT JOIN DRGs.T_PATIENTDRG B ON A.ID =B.PARID  \n");
		sql.append("    LEFT JOIN DRGs.T_ANNUALSTD C ON B.PARDRG_CODE=C.AS_DRG_CODE   \n");
		sql.append("    where C.AS_YEAR=:benchmarkYear \n");
		sql.append("      and to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n");
		   
		List<Object[]> finddiagnose7_1 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return finddiagnose7_1;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable89(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable89(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    select a.OUT_DEP,count(a.OUT_DEP),round(ratio_to_report(count(a.OUT_DEP)) over(),4) *100,round(avg(b.as_avg_fee),2),round(avg(b.AS_AVG_DAYS),2), \n");
		sql.append("    SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END),ROUND(SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END)/COUNT(1)*100,2) \n");
		sql.append("     from DRGs.T_MEDICALRECORD A \n");
		sql.append("     left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("     left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("      where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("             AND b.AS_YEAR =:benchmarkYear \n");
		sql.append("              AND a.IN_DAYS <61 AND D.PARDRG_CODE IS NOT NULL   \n");
		sql.append("               AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("               \n");
		sql.append("               group by  a.OUT_DEP \n");
		sql.append("               order by  count(a.OUT_DEP) desc \n");
		List<Object[]> findtable89 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable89;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#finddiagnose7_2(java.util.Map)
	 */
	@Override
	public List<Object[]> finddiagnose7_2(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      select count(*),sum(tr2),sum(tr3),round(avg(tr4),2),round(avg(tr5),2),sum(tr6),sum(tr7) \n");
		sql.append("     from( \n");
		sql.append("     select a.OUT_DEP tr1,count(a.OUT_DEP) tr2,round(ratio_to_report(count(a.OUT_DEP)) over(),4) *100 tr3,round(avg(b.as_avg_fee),2) tr4,round(avg(b.AS_AVG_DAYS) ,2) tr5,SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END) tr6,ROUND(SUM(CASE WHEN a.out_way='5' THEN 1 ELSE 0 END)/COUNT(1)*100,2) tr7  \n");
		sql.append("     from DRGs.T_MEDICALRECORD A  \n");
		sql.append("     left join DRGs.T_PATIENTDRG D on d.PARID = a.ID   \n");
		sql.append("     left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE    \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID  \n");
		sql.append("      where  a.diagnose_code_main in (" + dephospcodeStr + ")  \n");
		sql.append("             AND b.AS_YEAR =:benchmarkYear  \n");
		sql.append("              AND a.IN_DAYS <61 AND D.PARDRG_CODE IS NOT NULL    \n");
		sql.append("               AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'   \n");
		sql.append("                 \n");
		sql.append("               group by  a.OUT_DEP  \n");
		sql.append("               order by  count(a.OUT_DEP) desc)  \n");
		List<Object[]> finddiagnose7_2 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return finddiagnose7_2;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable90(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable90(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("     select a.OUT_DEP,count(a.OUT_DEP), \n");
		sql.append("        sum(case when a.IN_DAYS <61 AND D.PARDRG_CODE IS NOT NULL then 1 else 0 end),round(SUM(b.AS_WEIGHT)/COUNT(d.PARID),2),SUM(b.AS_WEIGHT), \n");
		sql.append("         ROUND(SUM(a.COSTAMOUNT/b.AS_AVG_FEE)/COUNT(d.PARID),2),  \n");
		sql.append("         ROUND(SUM(a.IN_DAYS/b.AS_AVG_DAYS)/COUNT(d.PARID),2)  , \n");
		sql.append("         sum(case when b.as_risk_level='1'and a.OUT_WAY='5' then 1 else 0 end), \n");
		sql.append("         sum(case when b.as_risk_level='2'and a.OUT_WAY='5' then 1 else 0 end) \n");
		sql.append("      from DRGs.T_MEDICALRECORD A \n");
		sql.append("      left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("      left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("      LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("      where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("             AND b.AS_YEAR =:benchmarkYear  \n");
		sql.append("                AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 \n");
		sql.append("                group by  a.OUT_DEP \n");
		sql.append("                order by  count(a.OUT_DEP) desc \n");
		List<Object[]> findtable90 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable90;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#finddiagnose7_3(java.util.Map)
	 */
	@Override
	public List<Object[]> finddiagnose7_3(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("     select sum(tr2),sum(tr3),round(avg(tr4),2),sum(tr5),round(avg(tr6),2),round(avg(tr7),2),sum(tr8),sum(tr9) \n");
		sql.append("     from( \n");
		sql.append("     select a.OUT_DEP tr1,count(a.OUT_DEP) tr2, \n");
		sql.append("        sum(case when a.IN_DAYS <61 AND D.PARDRG_CODE IS NOT NULL then 1 else 0 end) tr3,round(SUM(b.AS_WEIGHT)/COUNT(d.PARID),2) tr4,SUM(b.AS_WEIGHT) tr5, \n");
		sql.append("         ROUND(SUM(a.COSTAMOUNT/b.AS_AVG_FEE)/COUNT(d.PARID),2) tr6,  \n");
		sql.append("         ROUND(SUM(a.IN_DAYS/b.AS_AVG_DAYS)/COUNT(d.PARID),2) tr7 , \n");
		sql.append("         sum(case when b.as_risk_level='1'and a.OUT_WAY='5' then 1 else 0 end) tr8, \n");
		sql.append("          sum(case when b.as_risk_level='2'and a.OUT_WAY='5' then 1 else 0 end) tr9 \n");
		sql.append("      from DRGs.T_MEDICALRECORD A \n");
		sql.append("      left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("      left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("       LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("       where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("              AND b.AS_YEAR =:benchmarkYear  \n");
		sql.append("                AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 \n");
		sql.append("                group by  a.OUT_DEP \n");
		sql.append("                order by  count(a.OUT_DEP) desc) \n");
		List<Object[]> finddiagnose7_3 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return finddiagnose7_3;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#finddiagnose7_4(java.util.Map)
	 */
	@Override
	public List<Object[]> finddiagnose7_4(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("     select  max(case when h.tr2=g.trr2 then h.tr1 end),  \n");
		sql.append("             max(case when h.tr4=g.trr4 then h.tr1 end), \n");
		sql.append("             max(case when h.tr5=g.trr5 then h.tr1 end), \n");
		sql.append("             max(case when h.tr6=g.trr6 then h.tr1 end), \n");
		sql.append("             max(case when h.tr7=g.trr7 then h.tr1 end) from( \n");
		sql.append("     (select max(tr2) trr2,max(tr4) trr4,max(tr5) trr5,max(tr6) trr6,max(tr7) trr7 from( \n");
		sql.append("     select a.OUT_DEP tr1,count(a.OUT_DEP) tr2, \n");
		sql.append("        round(SUM(b.AS_WEIGHT)/COUNT(d.PARID),2) tr4,SUM(b.AS_WEIGHT) tr5, \n");
		sql.append("         ROUND(SUM(a.COSTAMOUNT/b.AS_AVG_FEE)/COUNT(d.PARID),2) tr6,  \n");
		sql.append("         ROUND(SUM(a.IN_DAYS/b.AS_AVG_DAYS)/COUNT(d.PARID),2) tr7   \n");  
		sql.append("      from DRGs.T_MEDICALRECORD A \n");
		sql.append("      left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("      left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("       LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("       where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("              AND b.AS_YEAR =:benchmarkYear \n");
		sql.append("                AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 \n");
		sql.append("                group by  a.OUT_DEP \n");
		sql.append("                order by  count(a.OUT_DEP) desc)) )g, \n");
		sql.append("         (select a.OUT_DEP tr1,count(a.OUT_DEP) tr2, \n");
		sql.append("        round(SUM(b.AS_WEIGHT)/COUNT(d.PARID),2) tr4,SUM(b.AS_WEIGHT) tr5, \n");
		sql.append("         ROUND(SUM(a.COSTAMOUNT/b.AS_AVG_FEE)/COUNT(d.PARID),2) tr6,  \n");
		sql.append("         ROUND(SUM(a.IN_DAYS/b.AS_AVG_DAYS)/COUNT(d.PARID),2) tr7    \n"); 
		sql.append("      from DRGs.T_MEDICALRECORD A \n");
		sql.append("      left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("      left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("       LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("       where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("              AND b.AS_YEAR =:benchmarkYear \n");
		sql.append("                AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("                 \n");
		sql.append("                group by  a.OUT_DEP \n");
		sql.append("                order by  count(a.OUT_DEP) desc) h \n");
		List<Object[]> finddiagnose7_4 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return finddiagnose7_4;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable91(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable91(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
		

		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("    select D.PARDRG_CODE,h.csd_drg_desc,count(D.PARDRG_CODE),round(ratio_to_report(count(D.PARDRG_CODE)) over(),4) *100,round(avg(b.as_avg_fee),2),round(avg(b.AS_AVG_DAYS),2) \n");
		sql.append("    from DRGs.T_MEDICALRECORD A \n");
		sql.append("    left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("    left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE   \n");
		sql.append("    LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("    LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=D.PARDRG_CODE \n");
		sql.append("     where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("            AND b.AS_YEAR =:benchmarkYear \n");
		sql.append("             AND a.IN_DAYS <61 AND D.PARDRG_CODE IS NOT NULL   \n");
		sql.append("              AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"'  \n");
		sql.append("              \n");
		sql.append("              group by  D.PARDRG_CODE,h.csd_drg_desc \n");
		sql.append("              order by  count(a.OUT_DEP) desc \n");
		List<Object[]> findtable91 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable91;//mdcCode
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.WordReportApplication#findtable92(java.util.Map)
	 */
	@Override
	public List<Object[]> findtable92(Map<String, Object> paramMap) {
		String reportDateFrom = paramMap == null ? "" : paramMap.get(
				"reportDateFrom").toString();
		String reportDateTo = paramMap == null ? "" : paramMap.get(
				"reportDateTo").toString();
		String  dephospcodeStr = paramMap == null ? "" : paramMap
				.get("dephospcodeStr").toString();
		String benchmarkYear = paramMap == null ? "" : paramMap.get(
				"benchmarkYear").toString();
		Map<String, Object> map = new HashMap<String, Object>();
	
		map.put("benchmarkYear", benchmarkYear);
		StringBuffer sql = new StringBuffer();
		sql.append("      select D.PARDRG_CODE,h.csd_drg_desc, \n");
		sql.append("          round(avg(b.AS_AVG_FEE),2), \n");
		sql.append("          ROUND(SUM(a.COSTAMOUNT/b.AS_AVG_FEE)/COUNT(d.PARID),2),  \n");
		sql.append("          round(avg(b.AS_AVG_DAYS),2), \n");
		sql.append("         ROUND(SUM(a.IN_DAYS/b.AS_AVG_DAYS)/COUNT(d.PARID),2) \n");
		sql.append("       from DRGs.T_MEDICALRECORD A \n");
		sql.append("       left join DRGs.T_PATIENTDRG D on d.PARID = a.ID  \n");
		sql.append("       left join DRGs.T_ANNUALSTD B  on d.pardrg_code = b.AS_DRG_CODE  \n"); 
		sql.append("        LEFT JOIN DRGs.T_DAILYRECORD X   ON X.DR_EPISODE_ID=a.RECORD_ID \n");
		sql.append("        LEFT JOIN DRGs.T_CLISPECDRGS H ON H.CSD_DRG_CODE=D.PARDRG_CODE  \n");
		sql.append("        where  a.diagnose_code_main in (" + dephospcodeStr + ") \n");
		sql.append("               AND b.AS_YEAR =:benchmarkYear  \n");
		sql.append("                 AND to_char(a.out_date,'yyyy-mm-dd') between '"+reportDateFrom+"' and '"+reportDateTo+"' \n"); 
		sql.append("                 \n");
		sql.append("                 group by   D.PARDRG_CODE,h.csd_drg_desc \n");
		sql.append("                 order by  count(a.OUT_DEP) desc \n");
		List<Object[]> findtable92 = getRepository()
				.createSqlQuery(sql.toString()).setParameters(map).list();
		return findtable92;//mdcCode
	}

}
